MYSQL count: select ALL of users, count active / total ads (2 accounts)

Question

I have a user table and an ad table

I would like to select ALL users from a table with two values: - active ads (classified .status = 'A') - all ads

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 does not work as expected

Answer 1

Use CASE inside COUNT.
ELSE case implies 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 

In addition, standard SQL is to group or group columns: MySQL GROUP BY extensions can produce misleading results because the engine guesses what you want.

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