i have 2 tables, users and follows. table follows has a column named status. I would like to count how many follows each user has grouping by the status.
The query below returns a record for each status type for each user.
SELECT users.name as user_name, f.status, count(f.id)
FROM users
JOIN application_follows f ON f.user_id = users.id
GROUP BY users.id, f.status
ORDER BY users.id
returns something like:
user_name status count
mike new 10
mike old 5
tom new 8
tom old 9
but i would like something more friendly like:
user_name new old
mike 10 5
tom 8 9
tried using group_concat and count but didnt work. Any clues?
SELECT user_name,
MAX(CASE WHEN status = 'new' THEN totalFollowers ELSE NULL END) `NEW`,
MAX(CASE WHEN status = 'old' THEN totalFollowers ELSE NULL END) `OLD`
FROM
(
SELECT users.name as user_name,
f.status,
count(f.id) totalFollowers
FROM users
LEFT JOIN application_follows f
ON f.user_id = users.id
GROUP BY users.id, f.status
) derivedTable
GROUP BY user_name
or maybe (not sure about this one)
SELECT users.name as user_name,
MAX(CASE WHEN f.status = 'new' THEN count(f.id) ELSE NULL END) `NEW`,
MAX(CASE WHEN f.status = 'old' THEN count(f.id) ELSE NULL END) `OLD`
FROM users
LEFT JOIN application_follows f
ON f.user_id = users.id
GROUP BY users.id, f.status