3

At 3.18.3, I am working with a standalone Geopackage attribute table containing data for a threatened bird species who are nesting in bird boxes.

The nesting boxes are periodically checked and the number of observed eggs are recorded. The data table looks like this:

box_id, observation_date, number_of_eggs:

1, 2021-05-17, 1
1, 2021-06-13, 0
2, 2021-05-17, 0
3, 2021-05-17, 1
3, 2021-06-09, NULL
3, 2021-06-13, 3

For each box_id I need to select the one record that contains the greatest number of eggs ever observed. Records with lesser, zero, or NULL egg values are to be ignored. If a box never had any eggs, it is likewise ignored.

In other words, did a given nest box produce eggs, and if so, what was the greatest number of eggs ever observed?

Using maximum("number_of_eggs",group_by:= "box_id") within Select by Expression, this is what I desire and expect (selected records are bolded):

1, 2021-05-17, 1
1, 2021-06-13, 0
2, 2021-05-17, 0
3, 2021-05-17, 1
3, 2021-06-09, NULL
3, 2021-06-13, 3

But this is what I see:

1, 2021-05-17, 1
1, 2021-06-13, 0
2, 2021-05-17, 0
3, 2021-05-17, 1
3, 2021-06-09, NULL
3, 2021-06-13, 3

It appears that the function is selecting all records for a given box_id, if that box_id ever contained one or more eggs.

How can I modify the expression to select just the single record with the maximum number of eggs for each box_id?

5
  • Are you applying that filter expression to "number_of_eggs" or "box_id"?
    – M Bain
    Jun 29, 2021 at 2:07
  • @MBain you may be on to something. But assuming that a filter expression would solve the problem, I do not know how to use the one associated with the maximum function. The accompanying statement says that filter = optional expression to use to filter features used to calculate aggregate. But what does that obtuse statement mean? I cannot find any instructions regarding how to use such a filter, or what it would look like...
    – Stu Smith
    Jun 29, 2021 at 3:45
  • I didn't mean the filter inside the aggregate function - I just meant the overall "Select by expression"/Advanced Filter. I think the Advanced filter expression should be: "number_of_eggs" = maximum("number_of_eggs",group_by:= "box_id"). At the moment its looks to me like the filter is selecting any rows where the box_id happens to equal the maximum number of eggs, ie the filter is set to "box_id" = maximum("number_of_eggs",group_by:= "box_id") - thats why box 2 is not picked up by the filter 2 != 0
    – M Bain
    Jun 29, 2021 at 3:47
  • 1
    Oh gosh, you are correct, and I feel pretty stupid! Thanks for your help, and please convert your comment to an answer so that I can credit you. FWIW, to exclude the zero values, I modified the expression to this: "number_of_eggs" > 0 and "number_of_eggs" = maximum("number_of_eggs",group_by:= "box_id")
    – Stu Smith
    Jun 29, 2021 at 4:30
  • Heh! We've all been there. :P
    – M Bain
    Jun 29, 2021 at 4:42

1 Answer 1

5

I think the Advanced filter expression should be:
"number_of_eggs" = maximum("number_of_eggs",group_by:= "box_id")

At the moment its looks to me like the filter is selecting any rows where the box_id happens to equal the maximum number of eggs, ie the filter is set to "box_id" = maximum("number_of_eggs",group_by:= "box_id") - thats why box 2 is not picked up by the filter, 2 != 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.