Count and group by day range

Question

SELECT 
loc.loc_pac as registro, datediff(dd, pac.pac_nasc, getdate()) as 'dias' 
from loc with (nolock), pac with (nolock), str with (nolock) 
where loc.loc_pac <> '' 
and (loc.loc_pac = pac.pac_reg) 
and (loc.loc_str = str.str_cod) 
order by dias

Generating the following result:

How do I group by tracks, like:

range | Qtd

0 - 20 | 8

20 - 30 | 2

30 - 40 | 2

...

Answer 1

I recommend making a track table or a view, where you configure the desired ranges. Then just make a SELECT with COUNT grouping by band.

Example:

create table faixas
(
  faixa varchar(10),
  inicio integer,
  fim integer

);


insert into faixas values ('0-10',0,10);
insert into faixas values ('11-20',11,20);
insert into faixas values ('21-30',21,30);
insert into faixas values ('31-40',31,40);
insert into faixas values ('41-50',41,50);
insert into faixas values ('51-60',51,60);
insert into faixas values ('61-70',61,70);
insert into faixas values ('71-80',71,80);
insert into faixas values ('81-90',81,90);
insert into faixas values ('91-200',91,200);

The query:

select
    f.faixa,
    count(r.codigo) as qtd
from faixas f
left outer join registros r on r.dias >= f.inicio and r.dias <= f.fim
group by f.faixa;

Upshot:

faixa   qtd
0-10    5
11-20   5
21-30   5
31-40   0
41-50   2
51-60   1
61-70   0
71-80   0
81-90   1
91-200  5

I put in the Sqlfiddle

---

Update

Now that you have been informed that you are using SQL Server 2008, and you cannot create a table for this, you can use the expression With:

with faixas as (

  select '0-10' as faixa, 0 as inicio ,10 as fim
  union
  select '11-20',11,20
  union
  select '21-30',21,30
  union
  select '31-40',31,40
  union
  select '41-50',41,50
  union
  select '51-60',51,60
  union
  select '61-70',61,70
  union
  select '71-80',71,80
  union
  select '81-90',81,90
  union
  select '91-200',91,200
)


select
f.faixa,
count(r.codigo) as qtd
from faixas f
left outer join registros r on r.dias >= f.inicio and r.dias <= f.fim
group by f.faixa;

I put in the Sqlfiddle