Sum of unique ids

Elastic Stack Elasticsearch
1

I have an aggregation that sums the column quantity, but I want it to sum the column quantity of unique IDS. Knowing that each unique id, has a unique quantity value.

For example, if I have :

id = 5 | quantity = 5
id = 5 | quantity = 5
id = 5 | quantity = 5
id = 6 | quantity = 3
id = 6 | quantity = 3

I want the sum to count this :

id = 5 | quantity = 5
id = 6 | quantity = 3

Which is 8.

This sum aggregation is used as a sub aggregation of another Terms aggregation which I won't bother explaining here as if I get the sum agg to work the other one is easy, I think. How to do that?

2

Do the IDs and quantities always stay constant? E.g. id 6 always has quantity 3? If a single ID can have different quantities, which one do you pick for the calculation?

I have some ideas how to solve this but didn't want to get too deep in the weeds before getting more info :slight_smile:

3

They don't always stay constant, but for each unique id there's a unique quantity. As shown in my example, if there are several unique ids they'll have the same unique quantity. So actually yes, they're constant in a way. Please do help :smiley:

E.g. id 6 always has quantity 3

Yes. Or always has quantity 5, or 10 or whatever, but they always have the same exact quantity if they are all id 6.

4

@polyfractal so any idea? Sorry to be persistant :smiley:

5

Ok cool, that makes things easier.

What I would do is:

  • terms aggregation on ID
    • max metric aggregation on quantity. Because all the quantities will be the same, a max (or min) will just return the value.
    • sum_bucket pipeline aggregation to add up all the quantities. Pipeline aggregations act on the result of other aggs, so this sum_bucket will point to the max metric and sum them all up.
1 Like
6

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.