I want to count the occurrences of all pairs of PRODUCT and CAT:
| PRODUCT | CAT |
|---|---|
| Foo | A |
| Bar | B |
| Bar | B |
| Foo | B |
| Bar | A |
| Bar | A |
| Bar | A |
| Bar | C |
This is what I want. Using CASE-WHEN leads me only to separate columns for CAT count.
| PRODUCT | CAT | COUNT |
|---|---|---|
| Foo | A | 1 |
| Foo | B | 1 |
| Bar | A | 3 |
| Bar | B | 2 |
| Bar | C | 1 |
CodePudding user response：
You can try this:
select PRODUCT, CAT, count(PRODUCT) as COUNT
from table_name
group by PRODUCT, CAT
Basically you group by PRODUCT and CAT, and use count() to get the count for each pair.