CODEWITHSUNDEEP
mysql
pimeys
pimeys

Reputation: 199

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?

Upvotes: 0

Views: 128

Answers (1)

jpw
jpw

Reputation: 44881

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

Upvotes: 1

Related Questions