SQL - Query para agrupar o n�mero de itens (COUNT) em intervalos de valores (CASE WHEN)

15/12/2018

Ol� pessoal, bom dia.
Sou novo aqui no f�rum e tamb�m iniciante em SQL. Gostaria da ajuda de voc�s para finalizar a query abaixo:

Preciso criar uma query que me d� a quantidade de pedidos por faixa de seus totais. O resultado final deve ficar assim:

k5 k10 k15 k20 acima20k TT
1030 84 10 4 8 1136

Para chegar nestes valores, tive que fazer 5 SELECTs e rod�-los separadamente:

select count (total) from pedidos where total <= 5000 and data between '2014-01-01' and '2014-12-31'
select count (total) from pedidos where total <= 10000 and total >5000 and data between '2014-01-01' and '2014-12-31'
select count (total) from pedidos where total <= 15000 and total >10000 and data between '2014-01-01' and '2014-12-31'
select count (total) from pedidos where total <= 20000 and total >15000 and data between '2014-01-01' and '2014-12-31'
select count (total) from pedidos where total >20000 and data between '2014-01-01' and '2014-12-31'

Da� tentei fazer tudo numa �nica query assim:

select
count (case when total <= 5000 then 1 else 0 end) as k5,
count (case when (total <= 10000 and total > 5000) then 1 else 0 end) as k10,
count (case when (total <= 15000 and total > 10000) then 1 else 0 end) as k15,
count (case when (total <= 20000 and total > 15000) then 1 else 0 end) as k20,
count (case when total > 20000 then 1 else 0 end) as acima20k,
count (total) as TT
from pedidos
where data between '2014-01-01' and '2014-12-31'

Mas o resultado que tive foi esse:

k5 k10 k15 k20 acima20k TT
1136 1136 1136 1136 1136 1136

Pelo que andei olhando na internet, um GROUP BY poderia me ajudar, mas n�o sei como faz�-lo.
Algu�m consegue me dar uma luz nisso?

Hugo Campigotto

Curtir t�pico + 0

Responder

Post mais votado

15/12/2018

nesse caso, utilize sum() no lugar de count().
assim:

select
	sum(case when total <=  5000 then 1 else 0 end) as k5,
	sum(case when total  >  5000 and total <= 10000 then 1 else 0 end) as k10,
	sum(case when total  > 10000 and total <= 15000 then 1 else 0 end) as k15,
	sum(case when total  > 15000 and total <= 20000 then 1 else 0 end) as k20,
	sum(case when total  > 20000 then 1 else 0 end) as acima20k,
	count(total) as TT
from pedidos
	where data between '2014-01-01' and '2014-12-31'

Emerson Nascimento

Responder

Gostei + 3

Mais Posts

15/12/2018

Emerson Nascimento

Explicando a postagem:
count() - conta o n�mero de registros com conte�do. n�o faz diferen�a se � 0 ou 1, ser� um registro contado (s� n�o conta se for null)
sum() - soma o conte�do do campo.

Ent�o, com a fun��o count(), usa-se NULL para ignorar o registro:

select
	count(case when total <=  5000 then 1 else null end) as k5,
	count(case when total  >  5000 and total <= 10000 then 1 else null end) as k10,
	count(case when total  > 10000 and total <= 15000 then 1 else null end) as k15,
	count(case when total  > 15000 and total <= 20000 then 1 else null end) as k20,
	count(case when total  > 20000 then 1 else null end) as acima20k,
	count(total) as TT
from pedidos
	where data between '2014-01-01' and '2014-12-31'

com o uso da fun��o count(), onde coloquei "then 1" poderia ser qualquer valor, por exemplo, "then 'A'", ou "then 350", ou "then getdate()" (qualquer conte�do). Tente a instru��o abaixo e voc� ver� que tamb�m funcionar�, porque basta que haja algum conte�do no campo:

select
	count(case when total <=  5000 then 1 else null end) as k5,
	count(case when total  >  5000 and total <= 10000 then getdate() else null end) as k10,
	count(case when total  > 10000 and total <= 15000 then 'A' else null end) as k15,
	count(case when total  > 15000 and total <= 20000 then 350 else null end) as k20,
	count(case when total  > 20000 then 'TESTE' else null end) as acima20k,
	count(total) as TT
from pedidos
	where data between '2014-01-01' and '2014-12-31'

Com a fun��o sum(), usa-se 0 para ignorar o registro:

select
	sum(case when total <=  5000 then 1 else 0 end) as k5,
	sum(case when total  >  5000 and total <= 10000 then 1 else 0 end) as k10,
	sum(case when total  > 10000 and total <= 15000 then 1 else 0 end) as k15,
	sum(case when total  > 15000 and total <= 20000 then 1 else 0 end) as k20,
	sum(case when total  > 20000 then 1 else 0 end) as acima20k,
	count(total) as TT
from pedidos
	where data between '2014-01-01' and '2014-12-31'

Responder

Gostei + 1

15/12/2018

Hugo Campigotto

nesse caso, utilize sum() no lugar de count().
assim:

select
	sum(case when total <=  5000 then 1 else 0 end) as k5,
	sum(case when total  >  5000 and total <= 10000 then 1 else 0 end) as k10,
	sum(case when total  > 10000 and total <= 15000 then 1 else 0 end) as k15,
	sum(case when total  > 15000 and total <= 20000 then 1 else 0 end) as k20,
	sum(case when total  > 20000 then 1 else 0 end) as acima20k,
	count(total) as TT
from pedidos
	where data between '2014-01-01' and '2014-12-31'

�timo, Emerson. Deu certinho. Muito obrigado.

Responder

Gostei + 0