I have 2 tables: Table 1 (User) contains names of users/leaders and Table 2 (Transactions) contains records of user transactions with a column named “reason”
There are several “reasons” which can be grouped into 2 categories.
User Table -------------- User Leader -------------- user1 Leader1 user2 Leader1 user3 Leader2 user4 Leader2 Transaction Table ----------------------------------- User Leader Reason ----------------------------------- user1 Leader1 Category 1 Reason 1 user1 Leader1 Category 1 Reason 1 user1 Leader1 Category 1 Reason 2 user1 Leader1 Category 1 Reason 2 user1 Leader1 Category 2 Reason 1 user1 Leader1 Category 2 Reason 1 user2 Leader1 Category 1 Reason 1 user2 Leader1 Category 1 Reason 1 user2 Leader1 Category 2 Reason 1 user2 Leader1 Category 2 Reason 1
I’m trying to count the number of transactions a user has made and group them into those 2 categories.
I have tried the following code which can get the count for Category 1 reason but is only showing users that have that reason. I was aiming to also show users without that reason but with a count of 0. This query also falls short of counting Category 2 Reason (I can’t even get near that just yet without breaking this simple one)
SELECT DISTINCT User.name, User.Leader, COUNT (reason) AS Reason FROM
Transactions AS TRACKER
INNER JOIN User AS GROUPS ON TRACKER.name=User.name
WHERE TRACKER.reason IN ('Category 1 Reason 1','Category 1 Reason 2','Category 1 Reason 3')
GROUP BY GROUPS.name, GROUPS.Leader
My expected result is:
-------------------------------------- User Leader Category 1 Category 2 -------------------------------------- User1 Leader1 4 2 User2 Leader1 2 2 User3 Leader2 0 0 User4 Leader2 0 0
but I am getting:
------------------------- User Leader Category 1 ------------------------- User1 Leader1 4 User2 Leader1 2
Thank you in advance for any help
Advertisement
Answer
I think you want conditional aggregation:
SELECT u.name, u.Leader,
SUM(CASE WHEN t.reason IN ('Category 1 Reason 1', 'Category 1 Reason 2', 'Category 1 Reason 3') THEN 1 ELSE 0 END) AS reason_count
FROM User u LEFT JOIN
Transactions t
ON t.name = u.name
GROUP BY u.name, u.Leader;
Note that this also uses a LEFT JOIN to keep all rows in users, if when there are no transactions at all.
If you want separate columns for each category reason, then extend the above logic:
SELECT u.name, u.Leader,
SUM(CASE WHEN t.reason IN ('Category 1 Reason 1') THEN 1 ELSE 0 END) as reason1_cnt,
SUM(CASE WHEN t.reason IN ('Category 1 Reason 2') THEN 1 ELSE 0 END) as reason2_cnt,
SUM(CASE WHEN t.reason IN ('Category 1 Reason 3') THEN 1 ELSE 0 END) as reason3_cnt
FROM User u LEFT JOIN
Transactions t
ON t.name = u.name
GROUP BY u.name, u.Leader;