# Using aggregate function: count() in where clause

**Category:** [AQL](https://discourse.openehr.org/c/aql/43)
**Created:** 2023-06-08 07:15 UTC
**Views:** 473
**Replies:** 2
**URL:** https://discourse.openehr.org/t/using-aggregate-function-count-in-where-clause/4079

---

## Post #1 by @CzarMich

I would like to find duplicate entries based on a specific field (OrderNumber). I tried out the query below but no success. Will be glad for any guidance. 
I am using Better Platform.

Thank you.

```
SELECT c/feeder_audit/originating_system_item_ids/id AS DocumentUID,
c/content[openEHR-EHR-OBSERVATION.laboratory_test_result.v1,'Laborbefund']/protocol[at0004]/items[at0094]/items[at0063,'Identifikation der Laboranforderung']/value/id AS OrderNumber,
c/context/other_context[at0001,'Tree']/items[openEHR-EHR-CLUSTER.case_identification.v0]/items[at0001]/value/value AS Encounter
FROM EHR e 
CONTAINS COMPOSITION c 
WHERE 
c/context/other_context[at0001,'Tree']/items[openEHR-EHR-CLUSTER.case_identification.v0]/items[at0001]/value/value = '122244405'
AND EXISTS c/feeder_audit/originating_system_item_ids/id
AND count(c/content[openEHR-EHR-OBSERVATION.laboratory_test_result.v1,'Laborbefund']/protocol[at0004]/items[at0094]/items[at0063,'Identifikation der Laboranforderung']/value/id) >=2
```

---

## Post #2 by @CzarMich

I also tried using  HAVING count ()
```
HAVING count((k/protocol[at0004]/items[at0094]/items[at0063,'Identifikation der Laboranforderung']/value/id) > =2)
```
But did not work.

---

## Post #3 by @sebastian.iancu

The `COUNT()` aggregate function was added relatively recent to [AQL specification](https://specifications.openehr.org/releases/QUERY/Release-1.1.0/AQL.html#_count) - you would have to check if the implementation you are using (Better) is implementing AQL Release 1.1.0 or an older release. 
Regarding the `HAVING` operator, it is not (yet) part of AQL specification.

---


**Canonical:** https://discourse.openehr.org/t/using-aggregate-function-count-in-where-clause/4079
**Original content:** https://discourse.openehr.org/t/using-aggregate-function-count-in-where-clause/4079