jamesmstone jamesmstone - 4 months ago 23
SQL Question

MySQL: conditional aggregation query with a group by

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.

Users Table:



userID
,
companyID
,
lastLogin


Desired Output



companyID
,
totalCompanyUsers
,
numUsersWhoLoggedInWithinLastMonthFromCompany


Attempt



SELECT
companyID, COUNT(userID) AS `numUsersWhoLoggedInWithinLastMonth`
FROM
Users
WHERE
IFNULL(TIMESTAMPDIFF(MONTH, lastLogin, NOW()),- 1) = 1
GROUP BY companyID;


Im struggling to figure out how to have two aggregation functions, where one is conditional.

Note: I'm using MySQL

Answer

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.

Comments