Why is GROUP BY not working in SQL Server?

Question

I have the following query:

SELECT TOP 500
        BusinessDate,
        BRANCH_CO_MNE,
        RIGHT(TRANS_INPUTTER, 5) 'USER_ID',
        CASE
            WHEN TRANS_TYPE LIKE '%Deposit%'
            THEN COUNT(*)
                END 'No of Cash Deposit'
FROM    test_link.MMBL_phase2.dbo.EB_MMBL_H_UAR_PROT
WHERE   BusinessDate = '2023-01-23'
GROUP BY    BusinessDate,
            BRANCH_CO_MNE,
            TRANS_INPUTTER,
            TRANS_TYPE
ORDER BY    USER_ID

Which returns the following results:

BusinessDate	BRANCH_CO_MNE	USER_ID	No of Cash Deposit
2023-01-23	BNK	10938	NULL
2023-01-23	BNK	10938	NULL
2023-01-23	BNK	10938	NULL
2023-01-23	BNK	10938	NULL
2023-01-23	BNK	10938	NULL
2023-01-23	BNK	11748	NULL
2023-01-23	BNK	11748	NULL
2023-01-23	BNK	11748	NULL
2023-01-23	BNK	11748	NULL
2023-01-23	BNK	11748	NULL
2023-01-23	BNK	11748	18
2023-01-23	BNK	11748	NULL

The NULL values are repeating while I have put the No of Cash Deposit in GROUP BY clause.

Shouldn't the results be like

BusinessDate	BRANCH_CO_MNE	USER_ID	No of Cash Deposit
2023-01-23	BNK	10938	NULL
2023-01-23	BNK	11748	18
2023-01-23	BNK	11748	NULL

Answer 1

I suspect the problem here is your attempt at conditional aggregation. For conditional aggregation the aggregation function doesn't go inside the CASE expression, the CASE expression is put inside the aggregate function. As a result of your method, you have to GROUP BY the column TRANS_INPUTTER because it's not being aggregated.

If you switch to conditional aggregation, then you can remove TRANS_TYPE from the GROUP BY. Also, you then need to change the GROUP BY on TRANS_INPUTTER to be RIGHT(TRANS_INPUTTER, 5).

SELECT TOP (500)
       BusinessDate,
       BRANCH_CO_MNE,
       RIGHT(TRANS_INPUTTER, 5) AS USER_ID,
       COUNT(CASE WHEN TRANS_TYPE LIKE '%Deposit%' THEN 1 END) AS [No of Cash Deposit]
FROM test_link.MMBL_phase2.dbo.EB_MMBL_H_UAR_PROT
WHERE BusinessDate = '2023-01-23'
GROUP BY BusinessDate,
         BRANCH_CO_MNE,
         RIGHT(TRANS_INPUTTER, 5)
ORDER BY USER_ID;