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

Navigation
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 up 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'm using MySQL, but I do not know if that's the case, I could see SQL in another database and translate to MySQL

The result of this SQL is 

mes qtd
3 | 5
4 | 8
5 | 23

I needed it to be: 

mes qtd
12 | 0 
 1 | 0
 2 | 0
 3 | 5
 4 | 8
 5 | 23
    
asked by anonymous 21.05.2018 / 17:44

3 answers

5

As select will return only existing data, you can create a table with months and make left join with this table, grouping by month and doing count in table mv_clients, for example: / p> 

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's an example working: link

    
21.05.2018 / 18:41
2

As there are no rows for the months, you can do it by subqueries: 

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 mv_mes table, and make a left join.

    
21.05.2018 / 18:27
0

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

The solution I got 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

In this way I create an index and force the sequence as I want

    
22.05.2018 / 13:46
UserForm in POS blocks client tab Start the combobox without any text