Select Count of Certain Rows as Column

Question

I have a table with a "status" column which accepts a TINYINT. For example:

name   | status
-------+--------
john   | 0
joe    | 1
johann | 0
jan    | 1
jane   | 0

How can I get a count of who is status 1 and who is status 0?

status1 | status0
--------+--------
2       | 3

Gordon Linoff · Accepted Answer · 2016-03-14 23:55:53Z

1

Just use conditional aggregation:

select sum(status = 1) as status1, sum(status = 0) as status0
from t;

In your case, you could also write this as:

select sum(status) as status1, sum(1 - status) as status0
from t;

answered Mar 14, 2016 at 23:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hannover Fist · Accepted Answer · 2016-03-14 23:57:35Z

1

I would use a CASE statement to check for your value.

SELECT SUM(CASE WHEN [status] = 1 THEN 1 ELSE 0 END) AS Status1,
    SUM(CASE WHEN [status] = 0 THEN 1 ELSE 0 END) AS Status0
FROM tbl;

answered Mar 14, 2016 at 23:57

Hannover Fist

10.9k1 gold badge21 silver badges42 bronze badges