# Aggregate Query - JAVA API query

**URL:** https://discuss.elastic.co/t/aggregate-query-java-api-query/368161
**Category:** Elasticsearch
**Tags:** language-clients
**Created:** [October 3, 2024, 4:24am UTC](https://discuss.elastic.co/t/aggregate-query-java-api-query/368161 "2024-10-03T04:24:07Z")
**Posts on this page:** 2
**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: [October 3, 2024, 4:24am UTC](https://discuss.elastic.co/t/aggregate-query-java-api-query/368161/1 "2024-10-03T04:24:07Z")

</div>

Hi there,  
I am looking for equivalent JAVA API query built using co.elastic.clients.elasticsearch.core.SearchRequest.

Here are the documents

```auto
{
   "lender_id": 27,
   "orig_date": "2014-02-23",
   "id": "1227-1",
   "status": 3,
   "principal":10
},
{
   "lender_id": 27,
   "orig_date": "2014-02-23",
   "id": "1227-2",
   "status": 3,
   "principal":20
},
{
   "lender_id": 27,
   "orig_date": "2014-02-23",
   "id": "1227-3",
   "status": 4,
   "principal":30
}

```

SQL Query

```auto
SELECT 
   count(*) as count, 
   ROUND( AVG( CAST( DATEDIFF(month, OrigDate, GETDATE()) as FLOAT) ), 0) as NoteAge,
   Principal = ISNULL(SUM(Principal), 0.00) 
FROM mytable
WHERE LenderID = 27 and status = 3;

```

- 

## I am looking for Elastic JAVA API query to achieve the same.

---

<div class="post-metadata">

### Author: ![ltrotta](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ltrotta/32/124003_2.png) [@ltrotta](https://discuss.elastic.co/u/ltrotta)
#### Post date: [October 3, 2024, 8:10am UTC](https://discuss.elastic.co/t/aggregate-query-java-api-query/368161/2 "2024-10-03T08:10:30Z")

</div>

Hello, this is a translation of the query that @RabBit_BR provided in the [previous thread](https://discuss.elastic.co/t/need-help-in-writing-aggregate-query/367982).

```java
esClient.search(s -> s
	.aggregations("group_by_summaryGroup", a -> a
		.terms(t -> t
			.field("group.keyword")
			.order(NamedValue.of("_key",SortOrder.Desc)
			)
		)
		.aggregations(Map.of("interest_paid_sum", Aggregation.of(ag -> ag
				.sum(su -> su
					.field("interest_paid")
			)),"outstanding_principal_sum", Aggregation.of(agg -> agg
				.sum(su -> su
					.field("principal_balance")
			)),"principal_repaid_sum", Aggregation.of(aggr -> aggr
				.sum(su -> su
					.field("principal_repaid")
			)),"invested_sum", Aggregation.of(aggre -> aggre
				.sum(su -> su
					.field("amount_participation")
			)),"note_count", Aggregation.of(aggreg -> aggreg
				.valueCount(v -> v
					.field("id")
			))))
	)
	.query(q -> q
		.bool(b -> b
			.filter(List.of(Query.of(qu -> qu
					.term(t -> t
						.field("is_sold")
						.value(FieldValue.of(true))
				)),Query.of(que -> que
					.term(t -> t
						.field("lender_id")
						.value(FieldValue.of(4477943))
				))))
		)
	)
	.size(0)
,Void.class);

```