I'm trying to create a record for each person and year, where there may be a year where the person doesn't exist. I've used cross join in the past, but I think the aggregate here might be messing with a previous approach.
The goal is one record per person/year combination, for all years 2021-2024, and a count of those records when the person exists. Any help appreciated!
with person as
(
select 2021 as cov_year, 'A' as person
union all
select 2021, 'A'
union all
select 2022, 'A'
union all
select 2024, 'A'
union all
select 2024, 'A'
union all
select 2024, 'A'
),
all_years as
(
select 2021 as year
union all
select 2022 as year
union all
select 2023 as year
union all
select 2024 as year
)
select
y.year, p.person, count(p.person) as ct
from
all_years y
left join
person p on y.year = p.cov_year
group by
y.year, p.person
order by
y.year;
Desired output:
year person ct
------------------
2021 A 2
2022 A 1
2023 A (0 or null)
2024 A 3
Thanks!
If you want result rows for all persons in all years then you'll to use a cross join between distinct person and year:
with person as
(
select 2021 as cov_year, 'A' as person
union all
select 2021, 'A'
union all
select 2022, 'A'
union all
select 2024, 'A'
union all
select 2024, 'A'
union all
select 2024, 'A'
),
all_persons as
(
select distinct person from person
),
all_years as
(
select 2021 as year
union all
select 2022 as year
union all
select 2023 as year
union all
select 2024 as year
)
select
y.year, ap.person, count(p.person) as ct
from
all_years y
cross join
all_persons ap
left join
person p on p.person = ap.person and y.year = p.cov_year
group by
y.year, ap.person
order by
y.year;