Geek asks and answers

Sql query for multiple count columns and group

I have the following table. Pupils:

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

I need a result that gives me

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

I have it, 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

offers?

+3
source share
2 answers

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 can also write a 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 am not sure if there is a performance advantage in the second query. My guess would be if it were probably very small. I prefer using the first query because I think it is clearer, but both should work. Also, I don't have an instance of MySql to test, but according to @Johan, ORDER BY clauses are not needed.

+11
source 
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

.
MySQL a GROUP BY , ORDER BY .

+1

More articles:


All Articles