SQL count occurrences of unique id on a specific date

Table example:

unique ID process date Amount
123 1/1/2023 10
123 1/1/2023 15
123 1/1/2023 20
123 1/1/2023 16
123 1/1/2023 90
124 1/1/2023 11
124 1/1/2023 10
124 1/1/2023 15
124 1/1/2023 20
124 1/1/2023 16
124 1/1/2023 90
124 1/1/2023 11

I would like to count the amounts per unique ID per process date.

This is what I’d like the output to display:

ID process date Count
123 1/1/2023 5
124 1/1/2023 7

Nothing that I have tried works.

>Solution :

You only need to GROUP BY both columns

SELECT
   ID,  process,    COUNT(Amount) count_amount
FROM MyTable
GROUP BY ID,    process
ORDER BY ID,    process

Leave a Reply