O Connor O Connor - 6 months ago 31
MySQL Question

How do I select count and minus sub count in MySQL?

Below is my database table.

id name status user_id
1 A 1 1
2 B 2 1
3 C 2 1
4 D 2 1
5 E 3 1

6 F 2 2
7 G 2 2
8 H 1 2


I would like to count all record where
user_id
= 1 and minus sub count records where
user_id
= 1 AND
status
= 2. The result should be as following.

count_all count_status_2 count_left user_id
5 3 2 1


Below is my sql query.

SELECT a.count_all, b.count_status_2, (a.count_all - b.count_all_bought) AS count_left, a.user_id
FROM
(
SELECT COUNT(*) AS count_all, user_id FROM table WHERE user_id = 1
) a
CROSS JOIN
(
SELECT COUNT(*) AS count_status_2 FROM table WHERE user_id = 1 AND status = 2
) b


But I am not sure if it is an efficient solution. Are there any better solutions?

Further, I would like to get the following result group by
user_id
.

count_all count_status_2 count_left user_id
5 3 2 1
3 2 1 2


How should I write the sql query to get the second result? Sorry if I ask for two solutions in one question, because it is similar, just add group by
user_id
.

Answer

You can instead use conditional aggregation:

SELECT COUNT(*) AS count_all, 
       SUM(status=2) AS count_status_2,
       COUNT(*)- SUM(status=2) AS count_left,
       user_id 
FROM table 
WHERE user_id = 1
-- Add GROUP BY and remove above WHERE for ALL users
GROUP BY user_id