Problem with a group by in a subselect - ORA-00979
542454Mar 2 2011 — edited Mar 2 2011Hi,
I have some difficulties with my query, I have every time an error ORA-00979 not a group by expression. Does anybody could help me, please ?
Thanks.
Here my query :
select
count(RES."ID") as "NbrRes",
RES.PATIENT_LASTNAME as "lastname",
RES.PATIENT_MAIDEN_NAME as "maiden name",
RES.PATIENT_FIRSTNAME as "firstname",
RES.EXTERNAL_NUMBER as "numero externe",
TRUNC(MONTHS_BETWEEN(RES.SAMPLE_SAMPLING_DATE,RES.PATIENT_BIRTHDATE)/12)+1 as "Age",
(select BOX_FULLNAME from HCQ_STORAGE_CONTAINER where BOX_ID=RES.STORAGE_BOX_ID) as "Box",
count(RES."ID")*RES.QUANTITY as "QtDispo",
(select SHORT_NAME from PRIMARY_UNIT where ID=RES.UNIT_ID) as "Unit"
from
HCQ_RESOURCE RES
group by
RES.PATIENT_LASTNAME,
RES.PATIENT_MAIDEN_NAME,
RES.PATIENT_FIRSTNAME,
RES.EXTERNAL_NUMBER,
RES.QUANTITY,
TRUNC(MONTHS_BETWEEN(RES.SAMPLE_SAMPLING_DATE,RES.PATIENT_BIRTHDATE)/12)+1
order by
TRUNC(MONTHS_BETWEEN(RES.SAMPLE_SAMPLING_DATE,RES.PATIENT_BIRTHDATE)/12)+1