Grouping by ranges? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Grouping by ranges?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-12-06 : 14:19:03

Ok, pulling my hair out again. I think this is one of those concepts that I just haven't grasped yet, and I'm figuring it's akin to the crosstab theory, which I have grasped but can't seem to apply to this problem.

Say I've got a table with a bunch of data (imagine that in a database!):


create table raw_data (days int,visits int,sales int)

insert into raw_data (days,visits,sales) values(0,1,5)
insert into raw_data (days,visits,sales) values(0,2,1)
insert into raw_data (days,visits,sales) values(1,1,3)
insert into raw_data (days,visits,sales) values(2,1,3)
insert into raw_data (days,visits,sales) values(2,2,6)
insert into raw_data (days,visits,sales) values(2,7,2)
insert into raw_data (days,visits,sales) values(4,1,2)
insert into raw_data (days,visits,sales) values(5,3,3)
insert into raw_data (days,visits,sales) values(6,3,3)
insert into raw_data (days,visits,sales) values(6,7,1)
insert into raw_data (days,visits,sales) values(7,6,4)
insert into raw_data (days,visits,sales) values(8,8,2)

I want to break these down into uneven groups; I want to look at sales grouped by number of visits, with the categories being 1,2-5,6-10, and the number of days, again with the categories being uneven; in this case 0,1,2-5,6-10,etc.

So desired output is:


days    visits    sales
0       1         5
0       2-5       1
1       1         3
2-5     1         5
2-5     2-5       11
2-5     6-10      2
6-10    2-5       3
6-10    6-10      7

I've typed and calculated that by hand, so my apologies if I did some bad math or something; hopefully the idea is clear.

I think this is time for "select case", but I can't seem to get a "group case" to work.

Thanks!
-b

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-06 : 14:36:51

create a table with your grouping, something like

create table ReportGroups (GroupDesc varchar(20) primary key, MinDays int, MaxDays int)

and add in your groups. Then just join to it, and GROUP BY GroupDesc. I personally never hard code in values like this using CASE or anything; I always try to create tables to store values and "min" and "max" days for each group and all that. Then, you never have to edit the stored procs or SQL statements to someday change your grouping.

So, something like this, then:


select 
  RG.GroupDesc, sum(Rd.visits) as Visits, SUM(Rd.sales) as Sales
from 
  raw_data RD
inner join
  ReportGroups RG
on 
  RD.Days between RG.MinDays and RG.MaxDays
group by
  GroupDesc

hope this helps .. techiniques like this (instead of CASE statements) will make your life much easier in the long run !

- Jeff

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-12-07 : 02:28:20

Jeff you are genius for pointing out things that we should see but never really see!

Jeff is absolutely correct -
a. this is not a cross-tab (this is just a grouping)
b. you wouldn't need SELECT CASE

If you create a ranges table like Jeff suggests, you will need two INNER JOINs to get the kind of output you need. I think this comes close to your requirements:


create table raw_data (days int,visits int,sales int)
go
create table ranges (rangeDesc varchar(20), startNum int, endNum int)
go

insert into raw_data (days,visits,sales) values(0,1,5)
insert into raw_data (days,visits,sales) values(0,2,1)
insert into raw_data (days,visits,sales) values(1,1,3)
insert into raw_data (days,visits,sales) values(2,1,3)
insert into raw_data (days,visits,sales) values(2,2,6)
insert into raw_data (days,visits,sales) values(2,7,2)
insert into raw_data (days,visits,sales) values(4,1,2)
insert into raw_data (days,visits,sales) values(5,3,3)
insert into raw_data (days,visits,sales) values(6,3,3)
insert into raw_data (days,visits,sales) values(6,7,1)
insert into raw_data (days,visits,sales) values(7,6,4)
insert into raw_data (days,visits,sales) values(8,8,2)
go

insert into ranges values('0',0,0)
insert into ranges values('1',1,1)
insert into ranges values('2-5',2,5)
insert into ranges values('6-10',6,10)
go

--select * from raw_data
--select * from ranges

select a.rangeDesc, b.rangeDesc, sum(sales) from raw_data inner join ranges a
	on days between a.startnum and a.endnum
	inner join ranges b
	on visits between b.startnum and b.endnum
group by a.rangeDesc, b.rangeDesc, a.startnum, b.startnum
order by a.startnum, b.startnum 
go

drop table raw_data
go
drop table ranges
go

Owais

We make a living out of what we get, but we make a life out of what we give.

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-12-11 : 22:53:23

That is a remarkably, and truly elegant solution. I am impressed!

Thanks
-b

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-11 : 23:36:06

Actually, to combine with what Owais has said, I think you just need 1 table:

DaysGroup, MinDays,MaxDays, VisitsGroup, MinVisits, MaxVisits

Then join from your data to this table where Days between MinDays and MaxDays , and Visits between MinVisits and MaxVisits . Then just group by DaysGroup and VisitsGroup, calculate the COUNT(*) and VOILA! you are done! just one join.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources