# Aggregation with unique count and sum: how to do it?

**URL:** <https://discuss.elastic.co/t/aggregation-with-unique-count-and-sum-how-to-do-it/234422>\
**Category:** Kibana\
**Created:** [May 26, 2020, 9:34pm UTC](https://discuss.elastic.co/t/aggregation-with-unique-count-and-sum-how-to-do-it/234422 "2020-05-26T21:34:00Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![simonlucalandi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/simonlucalandi/32/14990_2.png) [@simonlucalandi](https://discuss.elastic.co/u/simonlucalandi)\
**Post date:** [May 26, 2020, 9:34pm UTC](https://discuss.elastic.co/t/aggregation-with-unique-count-and-sum-how-to-do-it/234422/1 "2020-05-26T21:34:00Z")

</div>

Hello.  
We have some documents in ES (7.7.0) that represent some kind of ecommerce sales transactions, that can be described as the following table

```
| tx_id | item | cost | price |
-------------------------------------
| 1 | item1 | 10 | 15 |
| 2 | item2 | 15 | 20 | 
| 3 | item3 | 20 | 30 |
| 1 | item1 | 10 | 15 |
| 4 | item4 | 20 | 25 |
| 5 | item2 | 15 | 20 |
| 1 | item1 | 10 | 15 |
| 6 | item2 | 15 | 20 |
| 2 | item2 | 15 | 20 | 
| 7 | item1 | 10 | 15 | 

```

For reasons that are too long to explains, some of the documents contains "duplicated transactions".  
In this example data table, we have 3 documents with the same transaction\_id=1 and 2 documents with the same transaction\_id=2.  
We can't change the application that writes the transactions in the documents to avoid the duplication, so we have to live with that constraint.

What we are trying to achieve, is a consolidated report with the following data:

```
| item | unit_cost | unit_price | sold units | cost | revenue |
----------------------------------------------------------------
| item1 | 10 | 15 | 2 | 20 | 30 | <-- tx_id 1, 7, but tx_id=1 appears 3 times in the data table
| item2 | 15 | 20 | 3 | 45 | 60 | <-- tx_id 2, 5, 6, but tx_id=2 appears 2 times in the data table
| item3 | 20 | 30 | 1 | 20 | 30 | <-- tx_id 3
| item4 | 20 | 25 | 1 | 20 | 25 | <-- tx_id 4

```

We are struggling to get this aggregated table in kibana.  
We tried to create `terms` buckets for the fields `item`, `cost` and `price`, and calculating the metrics `unique count of tx_id` to get the `sold_units`, but then how to calculate the metrics for the `cost` and `revenue`?  
Using the `metric sum` will not work, because the sum of the `bucket item:item1 --> unit_cost:10 --> unit_price:15` contains 4 documents, because of the duplication of tx\_id=1

Any hints or suggestions?

Thanks.

SLL

---

<div class="post-metadata">

**Author:** ![myasonik](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/myasonik/32/62369_2.png) [@myasonik](https://discuss.elastic.co/u/myasonik)\
**Post date:** [May 26, 2020, 11:16pm UTC](https://discuss.elastic.co/t/aggregation-with-unique-count-and-sum-how-to-do-it/234422/2 "2020-05-26T23:16:45Z")

</div>

Hey @simonlucalandi! I'm not 100% certain if Kibana can produce something exactly like that but I think we can get close...

Can you can create a terms aggregation on your `tx_id`? Just trying to follow along to this previous issue I found: [Sum of unique ids](https://discuss.elastic.co/t/sum-of-unique-ids/134065/5)

---

<div class="post-metadata">

**Author:** ![simonlucalandi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/simonlucalandi/32/14990_2.png) [@simonlucalandi](https://discuss.elastic.co/u/simonlucalandi)\
**Post date:** [May 28, 2020, 7:54am UTC](https://discuss.elastic.co/t/aggregation-with-unique-count-and-sum-how-to-do-it/234422/3 "2020-05-28T07:54:37Z")

</div>

Hello!  
Thank you for the suggestion.

I'm now trying to use the "transform data" feature to create a pivot that removes the duplicated transaction (using "Max" aggregation) that save the clean data in a new index, and then create visualization on this new index.

It seams to be fit our needs.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 25, 2020, 7:54am UTC](https://discuss.elastic.co/t/aggregation-with-unique-count-and-sum-how-to-do-it/234422/4 "2020-06-25T07:54:44Z")

</div>

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