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
 General SQL Server Forums
 New to SQL Server Programming
 How to divide 2 rows and show the value

Author  Topic 

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-03-05 : 07:50:48
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 testValue
162430 Height 5 77
162430 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 me

P.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 HeightToWaistRatio
FROM
Tble
GROUP BY
UserId;[/code]
Go to Top of Page

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 1
Operand 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
Go to Top of Page

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 HeightToWaist
FROM
excz_measurements where testid in (5,9) and userid = 162430
GROUP BY
UserId;



now how to divide both by 100 to get ratio

P.V.P.MOhan
Go to Top of Page

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 HeightToWaistRatio
FROM
Tble
GROUP BY
UserId;[/code]
Go to Top of Page
   

- Advertisement -