sql query help on multiple count columns and group by

Question

i have the following table Students:

id | status | school | name
----------------------------
0  | fail   | skool1 | dan
1  | fail   | skool1 | steve
2  | pass   | skool2 | joe
3  | fail   | skool2 | aaron

i want a result that gives me

school | fail | pass  
---------------------
skool1 | 2    | 0   
skool2 | 1    | 1

I have this but it's slow,

SELECT s.school, (

SELECT COUNT( * ) 
FROM school
WHERE name = s.name
AND status = 'fail'
) AS fail, (

SELECT COUNT( * ) 
FROM school
WHERE name = s.name
AND status = 'pass'
) AS pass,

FROM Students s
GROUP BY s.school

suggestions?

rsbarro · Accepted Answer · 2011-05-12 01:26:34Z

11

Something like this should work:

SELECT 
    school,
    SUM(CASE WHEN status = 'fail' THEN 1 ELSE 0 END) as [fail],
    SUM(CASE WHEN status = 'pass' THEN 1 ELSE 0 END) as [pass]
FROM Students
GROUP BY school
ORDER BY school

EDIT
Almost forgot, but you could also write the query this way:

SELECT 
    school,
    COUNT(CASE WHEN status = 'fail' THEN 1 END) as [fail],
    COUNT(CASE WHEN status = 'pass' THEN 1 END) as [pass]
FROM Students
GROUP BY school
ORDER BY school

I'm not sure if there's any performance benefit with second query. My guess would be if there is it's probably very small. I tend to use the first query because I think it's more clear but both should work. Also, I don't have a MySql instance handy to test with, but according to @Johan the ORDER BY clauses are unnecessary.

edited May 12, 2011 at 1:26

answered May 11, 2011 at 23:23

rsbarro

27.4k9 gold badges73 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Johan · Accepted Answer · 2011-05-11 23:24:23Z

1

SELECT q.school, q.fail, q.failpass-q.fail as pass
FROM
  (
  SELECT s.school, sum(if(status = 'fail',1,0)) as fail, count(*) as failpass
  FROM students s
  GROUP BY s.school
  ) q

This way you save one conditional sum.
In MySQL a GROUP BY already orders the results, so a separate ORDER BY is not needed.

answered May 11, 2011 at 23:24

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Collectives™ on Stack Overflow

sql query help on multiple count columns and group by

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related