Using group by
-
Hi there,
Is it possible to use group by in a way that it uses a specified range rather than the distinct data in the table or view. For example, if i have a column size with 10 values - 1,2,5,2,5,8,40,22,58,40 and some other columns (maybe like product,month,category,client). If you write a query and group by size, you will have one row for each 1,2,5,8,22,58,40 depending on the aggregate function used.
But if wanted to group by a range, say one group is sizes between 1 and 10, then between 11 and 30 and then between 31 and 60. Is that possible? If yes, how? If no, how can you do such?
-
one way would be to wrap the size in a case statement then group by the case statement again
select
.......,
case when size >=1 <=10 then 1
else when size >=11 <=20 then 2
else when size >=21 <=30 then 3
end as SizeGroup
from
..............
where
..........
join
.............
group by
case when size >=1 <=10 then 1
else when size >=11 <=20 then 2
else when size >=21 <=30 then 3
end
-
You can also use a range table for this
INSERT INTO Ranges(MinVal,MaxVal)SELECT 1,10 UNION ALL
SELECT 11,30 UNION ALL
SELECT 31,60
SELECT r.MinVal,
r.MaxVal,
COUNT(*)
FROM Ranges r
INNER JOIN mytable t ON t.val BETWEEN r.MinVal AND r.MaxVal
GROUP BY r.MinVal,r.MaxVal
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Possible. here it is
if OBJECT_ID('tempdb..#temp') is not nulldrop table #temp
create table #temp (size int, nameofitem varchar(1) , price int)
insert into #temp
select 1 , 'a' , 10
union all select 5 , 'b' , 10
union all select 9 , 'c' , 10
union all select 13 , 'a' , 20
union all select 14 , 'd' , 20
union all select 15 , 'e' , 20
union all select 22 , 'a' , 30
union all select 26 , 'd' , 30
union all select 28 , 'e' , 30
select Grp = case when t.size between 1 and 10 then 'Group 1 to 10'
when t.size between 11 and 20 then 'Group 11 to 20'
else 'Group 21 to 30'
end
,Sumofprice = SUM(t.price)
from #temp t
group by case when t.size between 1 and 10 then 'Group 1 to 10'
when t.size between 11 and 20 then 'Group 11 to 20'
else 'Group 21 to 30'
end
-
Hi,
Thanks a lot. All approaches worked well
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply