Aprenda PL/SQL

Group in the format MM/YYYY but order in the format YYYY/MM

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Wed, 11 Jan 2017 1:58 pm

Hi, I have the following sql:
Code: Select all
select to_CHAR(dt_atendimento,'MM/YYYY'),
       count(*)
from tabela
where dt_atendimento between '01/10/2016' and '11/01/2017'
group by to_CHAR(dt_atendimento,'MM/YYYY')
I want to order the result of this sql output by year and then by month. However Oracle does not accept gives the message ORA-00979: not a GROUP BY expression. How can we make to display as MM/YYYY but order as YYYY/MM?
Robson Costa
Location: Campo Grande - MS

Poston Wed, 11 Jan 2017 3:18 pm

Good afternoon, try working with data instead of using the to_Char
Code: Select all
select to_char(dt_atendimento,'mm/yyyy'), qtd from (
select trunc(dt_atendimento,'month') dt_atendimento, count(*) qtd
from   tabela
where  dt_atendimento between '01-10-16' and '11-01-17'
group  by trunc(dt_atendimento,'month')
order  by trunc(dt_atendimento,'month'))
to see if it's as you want.
spernega
Location: São Paulo - SP



Return to SQL

Who is online

Users browsing this forum: No registered users and 3 guests