HELP! Use SQL SELECT to Get Percentage???

• Salvatore Conti Jr.

  Ten Centuries

  Points: 1155

  More actions

  January 28, 2005 at 1:36 pm

  #163176

  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!

• Antares686

  SSC Guru

  Points: 125444

  More actions

  January 28, 2005 at 1:54 pm

  #539610

  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.

• Salvatore Conti Jr.

  Ten Centuries

  Points: 1155

  More actions

  January 28, 2005 at 2:01 pm

  #539614

  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?