CODEWITHSUNDEEP
mysqlcount
coder101
coder101

Reputation: 1605

Two mysql counts with two different where conditions in one query

I have a table with two columns namely teacherid and sub_group. Now, sub_group can have values ranging from 0-100 where 0 means lectures for teachers and anything greater than 0 means tutorials. I want to be able to calculate the number of lectures and tutorials grouped by teachers.

So far i have two queries like

SELECT teacherid, count(*) as lectures FROM `ttresponsibility` where sub_group = 0
group by teacherid

SELECT teacherid, count(*) as tutorials FROM `ttresponsibility` where sub_group > 0
group by teacherid

I want to combine the results of the two into one resultset, something like

teacher    lectures   tutorials

1           15         10
2           14         8

Please suggest...

Upvotes: 0

Views: 1795

Answers (4)

Uriil
Uriil

Reputation: 12638

Almost same solution, as other guys offered, but with IF function:

    SELECT
        teacherid,
        SUM(IF(sub_group = 0, 1, 0)) as lectures,
        SUM(IF(sub_group > 0, 1, 0)) as tutorials,
    FROM ttresponsibility
    GROUP BY teacherid

For me it's easier to read

Upvotes: 0

Mike Brant
Mike Brant

Reputation: 71422

You can use CASE statement to get what you want here.

SELECT
    teacherid,
    SUM(CASE WHEN sub_group = 0 THEN 1 ELSE 0 END CASE) as lectures,
    SUM(CASE WHEN sub_group > 0 THEN 1 ELSE 0 END CASE) as tutorials,
FROM ttresponsibility
GROUP BY teacherid

Upvotes: 0

gbn
gbn

Reputation: 432742

This relies on COUNT ignoring NULLs (the missing ELSE in the CASE expression)

SELECT
   teacherid,
   count(CASE WHEN sub_group = 0 THEN 1 END) as lectures
   count(CASE WHEN sub_group > 0 THEN 1 END) as tutorials 
FROM
   `ttresponsibility`
group by teacherid

Upvotes: 1

Taryn
Taryn

Reputation: 247880

You can use the aggregate function with a CASE expression:

select teacherid,
  sum(case when sub_group = 0 then 1 else 0 end) lectures,
  sum(case when sub_group > 0 then 1 else 0 end) tutorials
from `ttresponsibility`
group by teacherid;

This will give you 3 columns, the teacherId and then the total lectured and tutorials in separate columns.

Upvotes: 4

Related Questions