SELECT COUNT(*) FROM a WHERE year = 2016 && month = 5 && date = 17 && user = 1
//return 1 row
SELECT COUNT(*) FROM a WHERE year = 2016 && month = 5 && date = 17 && user = 2
//return 0 row
I have 2 queries, I need to check user 1 and user 2, user 1 must have 1 row & user 2 must 0 row
My question is: is that possible to merge these 2 queries together
return in 1 row with 2 columns //return 1, 0
You can use sum() with a condition to count how many times the condition is true
SELECT SUM(user = 1) as u1,
SUM(user = 2) as u2
FROM a
WHERE year = 2016 and month = 5 and date = 17
Yes, this is called conditional aggregation :
SELECT count(CASE WHEN `user` = 1 THEN 1 END) as usr1_cnt,
count(CASE WHEN `user` = 2 THEN 1 END) as usr2_cnt
FROM a
WHERE year = 2016 and month = 5 and date = 17
Try this:
SELECT SUM(user = 1) AS user1, SUM(user = 2) AS user2
FROM a
WHERE year = 2016 AND month = 5 AND date = 17
The first field of the SELECT clause returns user = 1 occurrences, whereas the second field returns user = 2 occurrences.
try this,
SELECT
(
SELECT COUNT(*)
FROM a
WHERE year = 2016 && month = 5 && date = 17 && user = 1
) AS usr1,
(
SELECT COUNT(*)
FROM a
WHERE year = 2016 && month = 5 && date = 17 && user = 2
) AS usr2
You can also try this one:
SELECT SUM(user = 1) as user1,
SUM(user = 2) as user2
FROM a
WHERE year = 2016 and month = 5 and date = 17;
You can try this:
SELECT * from (
SELECT COUNT(*) count1 FROM a WHERE year = 2016 && month = 5 && date = 17 && user = 1) aa,
(SELECT COUNT(*) count2 FROM a WHERE year = 2016 && month = 5 && date = 17 && user = 2) bb;