SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 weight and length range
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Aged Yak Warrior

526 Posts

Posted - 11/06/2012 :  09:22:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/06/2012 :  09:49:28  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

magmo
Aged Yak Warrior

526 Posts

Posted - 11/06/2012 :  10:06:38  Show Profile  Reply with Quote
Excellent, Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/06/2012 :  12:34:32  Show Profile  Reply with Quote
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/

Go to Top of Page

magmo
Aged Yak Warrior

526 Posts

Posted - 11/06/2012 :  13:18:16  Show Profile  Reply with Quote
That sounds like a really good idea, but I'm not sure on how I would implement that.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/06/2012 :  13:46:04  Show Profile  Reply with Quote
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/

Go to Top of Page

magmo
Aged Yak Warrior

526 Posts

Posted - 11/07/2012 :  02:18:18  Show Profile  Reply with Quote
Thanks visakh16 for an excellent solution, it worked perfect.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000