Using aggregate function: count() in where clause

Specifications AQL
1

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
2

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.

3

The COUNT() aggregate function was added relatively recent to AQL specification - 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.

1 Like