Group different values of days per month (Oracle)

Question

Group different values of days per month (Oracle)

Asked 5 years, 2 months ago

Viewed 788 times

2

I have an appointment to do, it seems simple, but I’m not able to think of a way to make it work I have to select different values per day and group them by month... For example, a patient goes through a 10x hospital today, but in select he has to be added only 1x (remember that the select is from the table of care). Segue select

select to_char(a.dt_atendimento, 'MM') mes,
           to_char(a.dt_atendimento, 'DD') dia,
           count(distinct a.cd_paciente) qtd
      from atendime a
     where to_char(a.dt_atendimento, 'RRRR') = '2019'
     group by to_char(a.dt_atendimento, 'MM'),
              to_char(a.dt_atendimento, 'DD')
     order by 1, 2

One part of the result is this

Thanks in advance :)

And what problem are you finding in yours query? Can you make an example on SQL Fiddle?

– Sorack

2019/09/19 at 13:34
Using SELECT above as a suquery and the external query adding the amount only per month would not solve?

– anonimo

2019/09/19 at 13:35
@Sorack actually I have to "ignore" the value of the day column, but I can’t ignore it in the code because I need different values per day... That is, I need to remove it and group only per month, but the different number per day is necessary.

– Ítalo Salgado

2019/09/19 at 13:38
@I got it, there’s a simple solution to this

– Sorack

2019/09/19 at 13:39
@anonimo unfortunately was my first attempt, it continues bringing several results for the month 01, for example, behind 30 lines 011 with the value of each day, just does not display the column

– Ítalo Salgado

2019/09/19 at 13:40
@Sorack me a light, please kkk

– Ítalo Salgado

2019/09/19 at 13:41

Show 1 more comment

1 answer

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.

by Sorack • **27,430** points · Answer 1 · 2019-09-19T13:41:14+00:00

You can simply use your query above within another grouping together with the SUM:

SELECT x.mes,
       SUM(x.qtd) AS qtd
  FROM (
  SELECT TO_CHAR(a.dt_atendimento, 'MM') AS mes,
         TO_CHAR(a.dt_atendimento, 'DD') AS dia,
         COUNT(distinct a.cd_paciente) AS qtd
    FROM atendime a
   WHERE TO_CHAR(a.dt_atendimento, 'RRRR') = '2019'
   GROUP BY TO_CHAR(a.dt_atendimento, 'MM'),
            TO_CHAR(a.dt_atendimento, 'DD')
   ORDER BY 1, 2) x
 GROUP BY x.mes