# Need help in writing aggregate query

**URL:** https://discuss.elastic.co/t/need-help-in-writing-aggregate-query/367982
**Category:** Elasticsearch
**Tags:** language-clients
**Created:** [September 30, 2024, 1:00am UTC](https://discuss.elastic.co/t/need-help-in-writing-aggregate-query/367982 "2024-09-30T01:00:05Z")
**Posts on this page:** 4
**Page:** 1

<div class="post-metadata">

### Author: ![vkrishna](https://avatars.discourse-cdn.com/v4/letter/v/cab0a1/32.png) [@vkrishna](https://discuss.elastic.co/u/vkrishna)
#### Post date: [September 30, 2024, 1:00am UTC](https://discuss.elastic.co/t/need-help-in-writing-aggregate-query/367982/1 "2024-09-30T01:00:05Z")

</div>

Hi Team,  
I need help in writing below aggregate query.  
`Use co.elastic.clients.elasticsearch.core.SearchRequest co.elastic.clients.elasticsearch._types.query_dsl.Query. I want to convert below sql query to elastic query.`

```auto
SELECT
	group AS SummaryGroup,
    COUNT(*) AS NoteCount,
    SUM(AmountParticipation) AS Invested,
    SUM(PrincipalBalance) AS OutstandingPrincipal,
    SUM(PrincipalRepaid) AS PrincipalRepaid,
    SUM(InterestPaid) AS InterestPaid
FROM mytable
WHERE 
     IsSold = 1 AND LenderID = 4477943
GROUP BY group
ORDER BY group desc

```

JSON document looks like this

```auto
{
    "amount_participation: 15000.0,
    "group":"X1",
    "id": "263258-1",
    "interest_paid": 30.0,
    "is_sold": false,
    "lender_id": 4477943,
    "principal_balance": 1000.0,
    "principal_repaid": 200.0
}

```

Thank you

---

<div class="post-metadata">

### Author: ![RabBit\_BR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rabbit_br/32/82261_2.png) [@RabBit\_BR](https://discuss.elastic.co/u/RabBit_BR)
#### Post date: [September 30, 2024, 1:28am UTC](https://discuss.elastic.co/t/need-help-in-writing-aggregate-query/367982/2 "2024-09-30T01:28:34Z")

</div>

Hi @vkrishna

I think that query below might help you to start:

```auto
{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "is_sold": true
          }
        },
        {
          "term": {
            "lender_id": 4477943
          }
        }
      ]
    }
  },
  "aggs": {
    "group_by_summaryGroup": {
      "terms": {
        "field": "group.keyword",
        "order": {
          "_key": "desc"
        }
      },
      "aggs": {
        "note_count": {
          "value_count": {
            "field": "id"
          }
        },
        "invested_sum": {
          "sum": {
            "field": "amount_participation"
          }
        },
        "outstanding_principal_sum": {
          "sum": {
            "field": "principal_balance"
          }
        },
        "principal_repaid_sum": {
          "sum": {
            "field": "principal_repaid"
          }
        },
        "interest_paid_sum": {
          "sum": {
            "field": "interest_paid"
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

### Author: ![vkrishna](https://avatars.discourse-cdn.com/v4/letter/v/cab0a1/32.png) [@vkrishna](https://discuss.elastic.co/u/vkrishna)
#### Post date: [September 30, 2024, 4:09am UTC](https://discuss.elastic.co/t/need-help-in-writing-aggregate-query/367982/3 "2024-09-30T04:09:32Z")

</div>

@RabBit_BR  
I am looking for JAVA API query. Could you please help me on that.  
Thank you

---

<div class="post-metadata">

### Author: ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [September 30, 2024, 7:59am UTC](https://discuss.elastic.co/t/need-help-in-writing-aggregate-query/367982/4 "2024-09-30T07:59:13Z")

</div>

I wrote lot of examples [in this repo](https://github.com/dadoonet/elasticsearch-java-client-demo/blob/main/src/test/java/fr/pilato/test/elasticsearch/hlclient/EsClientIT.java). This could help you to convert the great answer from @RabBit_BR 😉