CODEWITHSUNDEEP
mysql
Benjamin W
Benjamin W

Reputation: 2848

Mysql merge 2 count query in one query

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

Upvotes: 2

Views: 290

Answers (6)

Jay Doshi
Jay Doshi

Reputation: 740

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;

Upvotes: 1

Jay Doshi
Jay Doshi

Reputation: 740

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;

Upvotes: 0

Hytool
Hytool

Reputation: 1368

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

Upvotes: 1

sagi
sagi

Reputation: 40481

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

Upvotes: 3

juergen d
juergen d

Reputation: 204766

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

Upvotes: 4

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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.

Upvotes: 2

Related Questions