Mysql merge 2 count query in one query

Question

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

Answer 1

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

Answer 2

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