jamesmstone jamesmstone - 5 months ago 9
SQL Question

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


Query attempt:

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


I'm struggling to figure out how to have two aggregation functions, where one is conditional.

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.