Help Grouping - 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 2005 Forums

Transact-SQL (2005)

Help Grouping

Author

Topic

smithygreg
Starting Member

37 Posts

Posted - 2007-08-13 : 13:56:57

Hi..
I need to write a query that performs some summary information on a group of packages and I need it to be broken down into weight groupings..
For example, I need the count and total weight of all packages from 1-5 lbs, then 6-10 pounds, then 11-20 lbs and so forth...

SO far, my only idea is to use unions like so...


SELECT '1-5' as WeightBreak, Sum(Weight) as TotalWeight, Count(Weight ) as Pkgs WHERE Weight BETWEEN 1 and 5
UNION
SELECT '6-10' as WeightBreak, Sum(Weight) as TotalWeight, Count(Weight ) as Pkgs WHERE Weight BETWEEN 6 and 10
UNION
SELECT '11-20' as WeightBreak, Sum(Weight) as TotalWeight, Count(Weight ) as Pkgs WHERE Weight BETWEEN 11 and 20

Is there a better way to go about this?
Thanks for any help!
-Greg

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 14:06:01

you can try something like this with CASE

select
case when weight between 1 and 5 then weight else 0 end,
case when weight between 5 and 10 then weight else 0 end,

I am not sure what your data looks like so depending on that this might or might not be helpful

Ashley Rhodes

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 14:17:16

USE NORTHWIND

GO

SELECT '1-100' as WeightBreak, Sum(freight) as TotalWeight, Count(freight ) as Pkgs from orders WHERE freight BETWEEN 1 and 100
UNION
SELECT '101-500' as WeightBreak, Sum(freight) as TotalWeight, Count(freight ) as Pkgs from orders WHERE freight BETWEEN 101 and 500
UNION
SELECT '>500' as WeightBreak, Sum(freight) as TotalWeight, Count(freight ) as Pkgs from orders WHERE freight >500

Ashley Rhodes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources