select two different counts sum from the same table

Question

I have a following table:

id  | R_ID  | is_assigned
-----------------
1   | X1    | 1
2   | X1    | 0
3   | X1    | 0
4   | X1    | 0

I want to get two different COUNT() values: one for all rows that belong to group X1. And next for rows that belong to group X1 and are not assigned, i.e. is_assigned value 0.

Output:

R_ID  | Total Records  | Remaining
----------------------------------
X1    | 4              | 3

This is as far as I could get:

SELECT t1.r_id, COUNT(t1.*) as total_records
FROM tasks t1
GROUP BY r_id

How do I get another count?

If you don't have a PRIMARY KEY, you don't really have a table. — Strawberry, May 16 '19 at 11:51

score 1 · Accepted Answer · answered May 16 '19 at 11:50

1

You can use conditional aggregation. I would recommend writing this in MySQL as:

SELECT t1.r_id, COUNT(*) as total_records,
       SUM(t1.is_assigned = 0) as remaining
FROM tasks t1
GROUP BY t1.r_id

answered May 16 '19 at 11:50

Gordon Linoff

1,242,037
58
646
786

select two different counts sum from the same table

1 Answers1