weight and length range - 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)

weight and length range

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-11-06 : 09:22:20

Hi

I have a table that hold information about a persons weight and length like this..

UserID (int)
UserWeight (nVarChar) values entered in kg
UserHeight (nVarChar) values entered in cm

I would like to retrieve statistic about the different weight and height values, but I want to count them in range so that the result would be something like this..

70-80kg | 5 | 80-90kg | 1

140-150 cm| 2 | 150-160cm| 4

I this possible?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-06 : 09:49:28

select [wieght 70-79] = sum(case when weight betwen 70 and 79 then 1 else 0 end)
, [weight 80-89] = sum(case when weight betwen 80 and 89 then 1 else 0 end)
, [height 200-209] = sum(case when height betwen 200 and 209 then 1 else 0 end)
from tbl

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-11-06 : 10:06:38

Excellent, Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-06 : 12:34:32

what if you range changes tommorow? If its onetime solution then fine otherwise I would suggest putting a range table to define your ranges which gives you flexibility to add/remove/modify ranges without changing code each time

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-11-06 : 13:18:16

That sounds like a really good idea, but I'm not sure on how I would implement that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-06 : 13:46:04

quote:
Originally posted by magmo

That sounds like a really good idea, but I'm not sure on how I would implement that.

Add a new table say RangeTable with fields RangeDescription,StartValue,EndValue with values like


RangeDescription,StartValue,EndValue
70-80,70,79
80-90,80,89
140-150,140,149
..

and your case logic should be replaced with a join to above table like

INNER JOIN RangeTable rt
ON weight BETWEEN rt.Start AND rt.End
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-11-07 : 02:18:18

Thanks visakh16 for an excellent solution, it worked perfect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources