Storm Spirit Storm Spirit - 4 months ago 9
SQL Question

SQL - count each column where each column = 1

I want to count teacher, organizer, administrator group by organization.

This is my current DB example:

enter image description here

Expected result will be:

teacher | organizer | administrator | organization_id
---------------------------------------------------
1 | 1 | 1 | 6035
5 | 4 | 1 | 12554


and what I've tried so far

SELECT
count(teacher),
count(organizer),
count(administrator),
organization_id
FROM users
WHERE
teacher = 1
AND organizer = 1
AND administrator = 1
GROUP BY organization_id;

Answer

Cause your column value is 0/1, so you can just use SUM to count nums:

SELECT
    SUM(teacher) as teacher,
    SUM(organizer) as organizer,
    SUM(administrator)as administrator,
    organization_id
FROM users GROUP BY organization_id
Comments