Error in queries leading to UNION ALL with aggregation functions
I had the following error:
psycopg2.errors.GroupingError: ERREUR: la colonne « _cc.cw_eid » doit apparaître dans la clause GROUP BY ou doit être utilisé dans une fonction d'agrégat
LINE 1: ...0, T1.C1, T1.C2, COUNT(T1.C3), T1.C4 FROM (SELECT _CC.cw_eid...
with:
rql = f"""
Any CC, CCT, ARK, COUNT(X) GROUPBY CC, EID, CCT, ARK ORDERBY COUNT(X) DESC LIMIT 21
Where C is Collection, C eid %(e)s, CI collection C, CI item X,
X is IN(Manuscript, Incunabula), CCI item X, CCI collection CC, CC eid EID,
CC displayed_title CCT, CC ark ARK
"""
indeed, the query is converted into:
SELECT T1.C0, T1.C1, T1.C2, COUNT(T1.C3) FROM (SELECT _CC.cw_eid AS C0, _CC.cw_displayed_title AS C1, _CC.cw_ark AS C2, _X.cw_eid AS C3, COUNT(_X.cw_eid) AS C4
FROM cw_Collection AS _CC, cw_CollectionItem AS _CCI, cw_CollectionItem AS _CI, cw_Incunabula AS _X
WHERE _CI.cw_collection=%(e)s AND _CI.cw_item=_X.cw_eid AND _CCI.cw_item=_X.cw_eid AND _CCI.cw_collection=_CC.cw_eid
UNION ALL
SELECT _CC.cw_eid AS C0, _CC.cw_displayed_title AS C1, _CC.cw_ark AS C2, _X.cw_eid AS C3, COUNT(_X.cw_eid) AS C4
FROM cw_Collection AS _CC, cw_CollectionItem AS _CCI, cw_CollectionItem AS _CI, cw_Manuscript AS _X
WHERE _CI.cw_collection=%(e)s AND _CI.cw_item=_X.cw_eid AND _CCI.cw_item=_X.cw_eid AND _CCI.cw_collection=_CC.cw_eid) AS T1
GROUP BY T1.C0,T1.C1,T1.C2
ORDER BY 4 DESC
LIMIT 21
While we want:
SELECT T1.C0, T1.C1, T1.C2, COUNT(T1.C3) FROM (SELECT _CC.cw_eid AS C0, _CC.cw_displayed_title AS C1, _CC.cw_ark AS C2, _X.cw_eid AS C3, COUNT(_X.cw_eid) AS C4
FROM cw_Collection AS _CC, cw_CollectionItem AS _CCI, cw_CollectionItem AS _CI, cw_Incunabula AS _X
WHERE _CI.cw_collection=%(e)s AND _CI.cw_item=_X.cw_eid AND _CCI.cw_item=_X.cw_eid AND _CCI.cw_collection=_CC.cw_eid
GROUP BY C0,C1,C2,C3
UNION ALL
SELECT _CC.cw_eid AS C0, _CC.cw_displayed_title AS C1, _CC.cw_ark AS C2, _X.cw_eid AS C3, COUNT(_X.cw_eid) AS C4
FROM cw_Collection AS _CC, cw_CollectionItem AS _CCI, cw_CollectionItem AS _CI, cw_Manuscript AS _X
WHERE _CI.cw_collection=%(e)s AND _CI.cw_item=_X.cw_eid AND _CCI.cw_item=_X.cw_eid AND _CCI.cw_collection=_CC.cw_eid
GROUP BY C0,C1,C2,C3) AS T1
GROUP BY T1.C0,T1.C1,T1.C2
ORDER BY 4 DESC
LIMIT 21