| Author |
 Topic  |
|
|
magmo
Constraint Violating Yak Guru
465 Posts |
 Posted - 11/06/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 11/06/2012 : 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
Constraint Violating Yak Guru
465 Posts |
Posted - 11/06/2012 : 10:06:38
|
| Excellent, Thanks! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 11/06/2012 : 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
Constraint Violating Yak Guru
465 Posts |
Posted - 11/06/2012 : 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
India
48064 Posts |
Posted - 11/06/2012 : 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
Constraint Violating Yak Guru
465 Posts |
Posted - 11/07/2012 : 02:18:18
|
| Thanks visakh16 for an excellent solution, it worked perfect. |
|
|
| |
Topic |
|
weight and length range
