Count number of instances of a specific value and join results with another table

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

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;