SQL - count each column where each column = 1

Question

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

This is my current DB example:

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;

Possible duplicate of [mysql count group by having](http://stackoverflow.com/questions/7857538/mysql-count-group-by-having) — karan, Jul 11 '16 at 04:44

score 1 · Accepted Answer · answered Jul 11 '16 at 04:01

1

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

answered Jul 11 '16 at 04:01

Blank

12,308
1
14
32

score 0 · Answer 2 · answered Jul 11 '16 at 04:22

Based on your expected result, I think you should use or rather than and in your where statement.

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

SQL - count each column where each column = 1

2 Answers2