pimeys pimeys - 6 months ago 9
SQL Question

MySQL combine 2 different counts in one query

I have a table, that pretty much looks like this:

users (id INT, masterId INT, date DATETIME)


Every user has exactly one master. But masters can have n users.

Now I want to find out how many users each master has. I'm doing that this way:

SELECT `masterId`, COUNT(`id`) AS `total` FROM `users` GROUP BY `masterId` ORDER BY `total` DESC


But now I also want to know how many new users a master has since the last 14 days. I could do it with this query:

SELECT `masterId`, COUNT(`id`) AS `last14days` FROM `users` WHERE `date` > DATE_SUB(NOW(), INTERVAL 14 DAY) GROUP BY `masterId` ORDER BY `total` DESC


Now the question: Could I somehow get this information with one query, instead of using 2 queries?

jpw jpw
Answer

You can use conditional aggregation to do this by only counting rows for with the condition is true. In standard SQL this would be done using a case expression inside the aggregate function:

SELECT 
  masterId, 
  COUNT(id) AS total,
  SUM(CASE WHEN date > DATE_SUB(NOW(), INTERVAL 14 DAY) THEN 1 ELSE 0 END) AS last14days
FROM users 
GROUP BY masterId 
ORDER BY total DESC

Sample SQL Fiddle