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:

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

Shouldn’t the results be like

>Solution :

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;