I have a system where users log in from different companies. I'm trying to run a query to see the number of active users vs total number of users for each company.
SELECT companyID, COUNT(userID) AS `numUsersWhoLoggedInWithinLastMonth`
WHERE IFNULL(TIMESTAMPDIFF(MONTH, lastLogin, NOW()),- 1) = 1
GROUP BY companyID;
You're pretty close, you just need to put the conditions inside the aggregate function for the conditional aggregation you want to perform:
SELECT companyID, COUNT(userID) AS `totalCompanyUsers`, SUM(CASE WHEN TIMESTAMPDIFF(MONTH, lastLogin, NOW()) < 1 THEN 1 ELSE 0 END ) AS `numUsersWhoLoggedInWithinLastMonth` FROM Users GROUP BY companyID;
The results are still grouped by
companyID, but the second aggregation function performs a sum of 1s and 0s depending on whether the user logged in within the last month or not.