# Using aggregate function: count() in where clause

**URL:** <https://discourse.openehr.org/t/using-aggregate-function-count-in-where-clause/4079>\
**Category:** AQL\
**Tags:** aql\
**Created:** [8 June 2023 07:15 UTC](https://discourse.openehr.org/t/using-aggregate-function-count-in-where-clause/4079 "2023-06-08T07:15:29Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![CzarMich](https://discourse.openehr.org/user_avatar/discourse.openehr.org/czarmich/32/2996_2.png) [@CzarMich](https://discourse.openehr.org/u/CzarMich)\
**Post date:** [8 June 2023 07:15 UTC](https://discourse.openehr.org/t/using-aggregate-function-count-in-where-clause/4079/1 "2023-06-08T07:15:29Z")

</div>

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.

```auto
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

```

---

<div class="post-metadata">

**Author:** ![CzarMich](https://discourse.openehr.org/user_avatar/discourse.openehr.org/czarmich/32/2996_2.png) [@CzarMich](https://discourse.openehr.org/u/CzarMich)\
**Post date:** [8 June 2023 13:02 UTC](https://discourse.openehr.org/t/using-aggregate-function-count-in-where-clause/4079/2 "2023-06-08T13:02:40Z")

</div>

I also tried using HAVING count ()

```auto
HAVING count((k/protocol[at0004]/items[at0094]/items[at0063,'Identifikation der Laboranforderung']/value/id) > =2)

```

But did not work.

---

<div class="post-metadata">

**Author:** ![sebastian.iancu](https://discourse.openehr.org/user_avatar/discourse.openehr.org/sebastian.iancu/32/31_2.png) [@sebastian.iancu](https://discourse.openehr.org/u/sebastian.iancu)\
**Post date:** [11 June 2023 15:29 UTC](https://discourse.openehr.org/t/using-aggregate-function-count-in-where-clause/4079/3 "2023-06-11T15:29:23Z")

</div>

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.