Say I have a table that looks like this:
| membership_id | service | frequency |
|---|---|---|
| 1 | A | monthly |
| 2 | A | quarterly |
| 3 | A | yearly |
| 4 | A | monthly |
| 5 | A | quarterly |
| 6 | A | yearly |
| 7 | B | monthly |
| 8 | B | monthly |
| 9 | B | monthly |
The query
select
service, frequency, count(membership_id)
from table
group by service, frequency
will yield these results:
| service | frequency | count |
|---|---|---|
| A | monthly | 2 |
| A | quarterly | 2 |
| A | yearly | 2 |
| B | monthly | 3 |
Is there a way to return ALL combinations of group-by dimensions, and return "0" where there is no results? Like so:
| service | frequency | count |
|---|---|---|
| A | monthly | 2 |
| A | quarterly | 2 |
| A | yearly | 2 |
| B | monthly | 3 |
| B | quarterly | 0 |
| B | yearly | 0 |
CodePudding user response：
You can use a cross join to give you every possible combination of service and frequency, and then left join to that in order to count non-null membership id's.
with cte AS (
select distinct a.service, b.*
from table a
cross join (
select frequency
from table
) b
)
select
a.service,
a.frequency,
count(membership_id)
from cte a
left join table b
ON a.service = b.service
and a.frequency = b.frequency
group by 1,2
order by a.service, a.frequency