How to achieve sum(distinct(columnName)) in Elastic search with out using terms and cardinality in aggregation

Suppose this is the record set in sql.

number net amt total amount
229W0330014229 0.69 11.07
229W0330014229 0.05 11.07
21380365288472 26.25 44.02
21380365288472 1.2 44.02
64950353437733 19.95 57.39
64950353437733 2.53 57.39
64950352648123 11.55 37.6
64950352648123 1.76 37.6
65410310307858 1.12 16.56
65410310307849 1.12 16.56
800W0346263038 0.8 11.81
Total: 11 67.02 195.01

And expected result from ES aggregation bucket is :-

ES net amount ES results total amount number distinct count
30.49000007 88.94999981
36.53000091 106.0599976
67.02 195.001 7

I achieved the above results in ES by using below query, but unwanted buckets get created due to which performance issue gets created for millions of records, please help how this could be done without using terms query to get total_amount sum.

"aggs": {
        "accessorials": {
          "date_histogram": {
            "field": "invoicedt",
            "interval": "year",
            "format": "Y-MM-dd:Y",
            "keyed": true
          },
          "aggs": {
            "amount": {
              "sum": {
                "field": "netamt"
              }
            },
            "total_amount": {
              "terms": {
                "field": "number",
                "size":"2147483647"
              },
              "aggs": {
                "MAX_COST": {
                  "max": {
                    "field": "total_amount"
                  }
                }
              }
            },
            "derived_total_amount": {
              "sum_bucket": {
                "buckets_path": "total_amount>MAX_COST"
              }
            },
            "distinct_number": {
            "stats_bucket": {
                "buckets_path": "total_amount._count"
            }
        }

The total_amount bucket is not required and i want to get rid of it, although i want to fetch sum of total_amount by number. Can we do this through scripts? is it a good approach? if yes How?

Need sum of total amount group by number field. An euivalent sql query would be:-

SELECT sum(distinct total_amount),number, sum(netamt) from table WHERE id = 6 group by number;

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