CODEWITHSUNDEEP
mysqlcountleft-join
Demiro-FE-Architect
Demiro-FE-Architect

Reputation: 3740

MYSQL count: select ALL from users, count active/total ads (2 counts)

I have a table of users and a table of classifieds

I would like to select ALL users from the table with 2 counts: - active classifieds (classifieds.status = 'A') - all classifieds

I tried something like this:

SELECT u.*, count(c.id) as total_active, COUNT( c2.id ) AS total
FROM `users` u
LEFT join `classifieds` c ON c.user_id=u.id AND c.status = 'A'
LEFT join `classifieds` c2 ON c2.user_id=u.id
GROUP BY u.id

but it is not working as expected

Upvotes: 4

Views: 807

Answers (1)

gbn
gbn

Reputation: 432657

Use a CASE inside the COUNT.
The ELSE case is implied NULL which is ignored by COUNT(column)

SELECT 
   u.*, 
   count(CASE WHEN c.status = 'A' THEN c.id END) as total_active, 
   COUNT(c.id) AS total
FROM 
    `users` u
    LEFT JOIN 
    `classifieds` c ON c.user_id = u.id 
GROUP BY 
    u.id -- fix this

Also, standard SQL is to either group on or aggregate columns: the MySQL GROUP BY extensions can give misleading results because the engine guesses what you want.

Try this query with SET SQL_MODE = 'ONLY_FULL_GROUP_BY' to see what I mean...

Upvotes: 6

Related Questions