Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
hello all ,i have table in which i will get height and breadth. Like for UserId 162430 have 2 rows and the result comes like this :[CODE]UserID test testID testValue162430 Height 5 77162430 Waist circumference 9 41.00[/CODE]so here test column have Height and Waist circumference and i need to divide test value show it's result for userid 162340...suggest meP.V.P.MOhan
James K
Master Smack Fu Yak Hacker
3873 Posts
Posted - 2013-03-05 : 08:39:32
[code]SELECT UserId, MAX(CASE WHEN test = 'Height' THEN testValue END )/ MAX(CASE WHEN test = 'Waist Circumference' THEN testValue END ) AS HeightToWaistRatioFROM TbleGROUP BY UserId;[/code]
mohan123
Constraint Violating Yak Guru
252 Posts
Posted - 2013-03-05 : 08:53:35
HERE i am getting this
Msg 8117, Level 16, State 1, Line 1Operand data type varchar is invalid for divide operator.
because this testValue is varchar need to convert into int...because varchar doesnot support divided by....P.V.P.MOhan
mohan123
Constraint Violating Yak Guru
252 Posts
Posted - 2013-03-05 : 08:57:59
yeah i got converting them into float
SELECT UserId, MAX(CASE WHEN test = 'Height' THEN convert(float,testValue) END )/ MAX(CASE WHEN test = 'Waist Circumference' THEN convert(float,testValue) END ) AS HeightToWaistFROM excz_measurements where testid in (5,9) and userid = 162430GROUP BY UserId;
now how to divide both by 100 to get ratioP.V.P.MOhan
James K
Master Smack Fu Yak Hacker
3873 Posts
Posted - 2013-03-05 : 08:58:27
[code]SELECT UserId, 100.0*MAX(CASE WHEN test = 'Height' THEN CAST(testValue AS FLOAT) END )/ MAX(CASE WHEN test = 'Waist Circumference' THEN CAST(testValue AS FLOAT) END ) AS HeightToWaistRatioFROM TbleGROUP BY UserId;[/code]