January 28, 2005 at 1:36 pm
I really need help here...I think I know what I need to do, but can not seem to figure it out...
Here is what I have...I have to views that receive values:
1st Select statement:
SELECT COUNT([Overall Rating]) FROM S526960.HDPIMaster WHERE Location = 'HBUS' AND [Overall Rating] = 'Good'
2nd Select Statment:
SELECT COUNT([Overall Rating]) FROM S526960.HDPIMaster WHERE Location = 'HBUS'
I need to write the 3rd statment that will divide the result from view1 from view2....
I hope that someone can help!
January 28, 2005 at 1:54 pm
You need to do a cast on the results from count to a decimal because it will return an INT value. And personally I would right as one query and not use the 2 views to limit work.
-- Returns percentage with 2 decimal places.
SELECT Cast(ROUND(((CAST(SUM(CASE WHEN [Overall Rating] = 'Good' THEN 1 ELSE 0 END) AS decimal(10,5)) / CAST(COUNT(*) AS decimal(10,5)) * 100.00), 2) as decimal(5,2)) FROM S526960.HDPIMaster WHERE Location = 'HBUS'
Or
-- Returns percentage value as whole number.
SELECT Cast(ROUND(((CAST(SUM(CASE WHEN [Overall Rating] = 'Good' THEN 1 ELSE 0 END) AS decimal(10,5)) / CAST(COUNT(*) AS decimal(10,5)) * 100.00), 0) as int) FROM S526960.HDPIMaster WHERE Location = 'HBUS'
Using the 2 views means extra reads against the table this will eliminate that.
January 28, 2005 at 2:01 pm
That is EXACTLY what I was looking for...
I ran it through my SQL Query Analyzer...and it cambe back with an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.
I am not sure which comma is throwing the error?
What do you think?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply