Forum Stats

  • 3,758,070 Users
  • 2,251,327 Discussions
  • 7,870,015 Comments

Discussions

How to 'CASE' in a GROUP BY statement?

Melina Schreiber
Melina Schreiber Member Posts: 9 Green Ribbon
edited Sep 20, 2021 8:46PM in SQL Developer

Hi there! I'm sure my question is dumb, but last time I've used SQL was 14 years ago, and I'm new to Oracle, so I'm pretty lost.

I need to write a query to retrieve total number of medical appointments that were actually served in a year, sorted by month. So far, I've got this code:

SELECT 
COUNT(t4.APPOINTMENT_STATUS_TYPE_KEY)
FROM HEALTH_KERNEL.APPOINTMENT turnos
    INNER JOIN HEALTH_KERNEL.AGENDA_SCHEDULE t1    ON t1.AGENDA_SCHEDULE_KEY = turnos.AGENDA_SCHEDULE_KEY
    INNER JOIN HEALTH_KERNEL.AGENDA t2             ON t2.AGENDA_KEY = t1.AGENDA_KEY
    INNER JOIN EHCOS.LABEL t3                      ON t2.LONG_DESC_LABEL_KEY = t3.LABEL_CODE
    INNER JOIN HEALTH_KERNEL.APPOINTMENT_STATUS t4 ON t4.APPOINTMENT_KEY = turnos.APPOINTMENT_KEY 
    INNER JOIN EHCOS.TYPE_MASTER tm                ON tm.TYPE_MASTER_KEY = t4.APPOINTMENT_STATUS_TYPE_KEY
WHERE tm.TYPE_MASTER_KEY = 1000075 -- served appointment ID
AND EXTRACT(YEAR FROM turnos.DATE_TIME) = ${Año}
GROUP BY extract(MONTH FROM turnos.DATE_TIME)
ORDER BY extract(MONTH FROM turnos.DATE_TIME);

It's actually working correctly, but I'd like to add a column showing which month each value belongs to, but after serching for 2 hours and a half, I couldn't figure it out. Can someone please give me a tip?


Tagged:

Best Answers

Answers