How to mount this SQL to bring months that are not in the list?

Asked

Viewed 315 times

2

The SQL below brings the number of entries made month by month, until then blz. I needed to show the last 6 months, even if it was zero, but in this select it would only bring when there are records.

select 
  extract(month from data_cad) as mes,
  count(*) as qtd
from mv_clientes
where (ativo not in('C'))
and (data_cad between '2017-12-01' and '2018-05-31')
group by 1

I am using Mysql, but do not know comes to the case, I could see SQL on another basis and translate pro Mysql

The result of this SQL is this

mes qtd
3 | 5
4 | 8
5 | 23

I needed you to be:

mes qtd
12 | 0 
 1 | 0
 2 | 0
 3 | 5
 4 | 8
 5 | 23
mysql sql

3 answers

5


Like the select will return only existing data, you can create a table with the months and do the left join with this table, grouping by month and doing the count in the mv_clients table, for example:

select x.mes, count(m.data_cad) qtd
  from meses x
  left join mv_clientes m on x.mes = extract(month from m.data_cad)
  group by x.mes

Here a working example: http://sqlfiddle.com/#! 9/b2072d/1

  • Good, this tip even gave me another idea, in this table of months I can have the months shortened and whole, Ex, JANEIRO, JAN, so I avoid the command with those old routines with CASE to keep picking the names of the months

  • I was using UNION to bring the months zero, but the select gets huge and horrible

  • yes, the left join already solved, and already did Join, by the names of the months is already easy

2

As there are no lines for the months, can do by Queries:

select 
(select count(*) as jan from  mv_clientes where (ativo not in('C'))
and  extract(month from data_cad) = 1 and  extract(year from data_cad) = 2018 JAN,

(select count(*) as jan from  mv_clientes where (ativo not in('C'))
and  extract(month from data_cad) = 2 and  extract(year from data_cad) = 2018 FEV,

(select count(*) as jan from  mv_clientes where (ativo not in('C'))
and  extract(month from data_cad) = 3 and  extract(year from data_cad) = 2018 MAR,

....
(select count(*) as jan from  mv_clientes where (ativo not in('C'))
and  extract(month from data_cad) = 12 and  extract(year from data_cad) = 2018 DEZ

without from

Or create a table mv_mes, and make a left Join.

0

Following the answers of the friends I found another difficulty, when I do the select I need to sort by date/ month, but in the months that have nothing it brings zero and there is no date for ordering, this way I can not have the result 12/1/2/3/4/5 that would be the last 6 months.

The solution I arrived at was this::

select m.indice, m.mes, count(cli.cod_id) as total 
from ( 
select 1 as indice, 05 as mes 
union all 
select 2 as indice, 04 as mes 
union all 
select 3 as indice, 03 as mes 
union all 
select 4 as indice, 02 as mes 
union all 
select 5 as indice, 01 as mes 
union all 
select 6 as indice, 12 as mes ) as m left 
join mv_clientes cli on(extract(month from cli.data_cad) = m.mes) 
and (cli.ativo not in('C')) 
group by 1, 2
order by 1 desc

That way I create an index and force the sequence as I want

Browser other questions tagged mysql sql

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