Conditional aggregation query with a group by

Question

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.

Not only column's name, but add some sample data and desired result. — Blank
– Blank, Commented Jul 6, 2016 at 5:19
Perfectly good question, an attempt was made and it's easy to visualise what is required based on the column names and the attempt that was made. Not all questions need sample data. — mjsqu
– mjsqu, Commented Jul 6, 2016 at 21:19

mjsqu · Accepted Answer · 2016-07-06 05:22:19Z

2

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.

answered Jul 6, 2016 at 5:22

mjsqu

5,4721 gold badge19 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jamesmstone Over a year ago

Thanks for quick reply, that unfortunately returns 0 for all numUsersWhoLoggedInWithinLastMonth

jamesmstone Over a year ago

I'm not sure if this would effect it, but users who have never logged in, ie some not active users, have a lastLogin value of Null

jamesmstone Over a year ago

I swapped your < for a > and it is now working ! thanks!!

jamesmstone Over a year ago

scrap that I was being stupid, yours works perfectly, thanks

Collectives™ on Stack Overflow

Conditional aggregation query with a group by

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related