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
 General SQL Server Forums
 New to SQL Server Programming
 How to divide 2 rows and show the value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 03/05/2013 :  07:50:48  Show Profile  Reply with Quote
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 :

UserID	test	testID	testValue
162430	Height	5	77
162430	Waist circumference	9	41.00


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
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 03/05/2013 :  08:39:32  Show Profile  Reply with Quote
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;
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 03/05/2013 :  08:53:35  Show Profile  Reply with Quote
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

India
252 Posts

Posted - 03/05/2013 :  08:57:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 03/05/2013 :  08:58:27  Show Profile  Reply with Quote
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;

Edited by - James K on 03/05/2013 09:00:04
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.05 seconds. Powered By: Snitz Forums 2000