Not a "Group By" expression-CodePudding

I'm facing ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" when I try to run this script in oracle

      SELECT m.working_minutes as minutes, sum(at.length_in_minutes)
  FROM medic m 
  inner join appointment a on a.medic_id = m.id
  inner join appointment_type at on at.id = a.appointment_type_id
  group by m.id
  HAVING TO_DATE(a.appointmet_date, 'dd/mm/yyyy') = TO_DATE(sysdate, 'dd/mm/yyyy');

but when I remove the group by m.id and then run the script as

      SELECT m.working_minutes as minutes, sum(at.length_in_minutes)
  FROM medic m 
  inner join appointment a on a.medic_id = m.id
  inner join appointment_type at on at.id = a.appointment_type_id

it gives me this error:

    ORA-00937: not a single-group group function
    00937. 00000 -  "not a single-group group function"

so, I don't know what to do

CodePudding user response：

You want to include the columns you are not aggregating by in the GROUP BY clause and your HAVING clause should be a WHERE clause that is applied before the aggregation.

SELECT m.working_minutes as minutes,
       sum(at.length_in_minutes)
FROM   medic m 
       INNER JOIN appointment a on a.medic_id = m.id
       INNER JOIN appointment_type at on at.id = a.appointment_type_id
WHERE  a.appointmet_date >= TRUNC(SYSDATE)
AND    a.appointmet_date <  TRUNC(SYSDATE)   INTERVAL '1' DAY
GROUP BY
       -- m.id,
       m.working_minutes;

Also, never use TO_DATE on a value that is already a DATE.

It sounds from your description that what you might want is:

SELECT m.id,
       MAX(m.working_minutes) as minutes,
       COALESCE(SUM(at.length_in_minutes), 0) AS time_worked
FROM   medic m 
       LEFT OUTER JOIN appointment a
       ON (   a.medic_id = m.id
          AND a.appointmet_date >= TRUNC(SYSDATE)
          AND a.appointmet_date <  TRUNC(SYSDATE)   INTERVAL '1' DAY)
       LEFT OUTER JOIN appointment_type at
       ON (at.id = a.appointment_type_id)
GROUP BY
       m.id;

db<>fiddle here