1

Using SQL Management studio 2012

I have a query similar to below but need to add the percentage column of the pass rate

SELECT 

pass,
fail,
total,
name

FROM table


pass  fail total  name     %
 3      1    4    Joe      75
 5      0    5     Matt    100
 1      1    2     Al      50
 7      0    7    Freda    100

What I've tried to get the result:

SELECT

name,
sum(pass) /Sum(total) *100.0 as pass_rate


FROM
(
SELECT 

pass,
fail,
total,
name

FROM table
)a

GROUP BY name

Result obtained when running above query(which is wrong): 

name    pass_rate

 Joe      100
 Matt     100
 Al        0
 Freda    100

Please help. Thankyou.

Prashant Barve
  • 4,105
  • 2
  • 33
  • 42
whitz11
  • 229
  • 2
  • 23

2 Answers2

2

Just move that Multiply by 100 inside to numerator

SELECT
name,
SUM(pass) * 100 /Sum(total) AS pass_rate
....
Prashant Barve
  • 4,105
  • 2
  • 33
  • 42
Prateek Shrivastava
  • 1,877
  • 1
  • 10
  • 17
1

You can try below

DEMO

select 
pass,
fail,
total,
name,(pass*1.0/total)*100 as pass_rate
from table

OUTPUT:

  pass  fail    total   name    pass_rate
    3        1       4       Joe    75.0
    5        0       5       Matt   100.0
    1        1       2       Al     50.0
    7        0       7       Freda  100.0
Fahmi
  • 37,315
  • 5
  • 22
  • 31