Reputation: 1605
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
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
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
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
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