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