By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table data
(
year int,
week int,
flag bit
);
insert into data (year, week, flag) values
(2021, 1, 0),
(2021, 1, 1),
(2021, 1, 1),
(2021, 2, 0),
(2021, 2, 0),
(2021, 2, 0),
(2021, 2, 0),
(2021, 3, 1);
select * from data;
| year | week | flag |
|---|---|---|
| 2021 | 1 | False |
| 2021 | 1 | True |
| 2021 | 1 | True |
| 2021 | 2 | False |
| 2021 | 2 | False |
| 2021 | 2 | False |
| 2021 | 2 | False |
| 2021 | 3 | True |
-- issue reproduction, how to combine these results?
-- no count for (2021, 1, true)
-- no count for (2021, 3, false)
select d.year,
d.week,
count(1) as countFalse
from data d
where d.flag = 0
group by d.year,
d.week;
select d.year,
d.week,
count(1) as countTrue
from data d
where d.flag = 1
group by d.year,
d.week;
select coalesce(f.year, t.year) as year,
coalesce(f.week, t.week) as week,
count(1) as countFalse,
t.countTrue
from data f
full join ( select d.year,
d.week,
count(1) as countTrue
from data d
where d.flag = 1
group by d.year,
d.week ) t
on t.year = f.year
and t.week = f.week
where f.flag = 0 --> issue: week 3 not available for flag = 0, results limited...
group by f.year,
t.year,
| year | week | countFalse |
|---|---|---|
| 2021 | 1 | 1 |
| 2021 | 2 | 4 |
| year | week | countTrue |
|---|---|---|
| 2021 | 1 | 2 |
| 2021 | 3 | 1 |
| year | week | countFalse | countTrue |
|---|---|---|---|
| 2021 | 1 | 1 | 2 |
| 2021 | 2 | 4 | null |
-- full outer join (NO where clause in final select)
with cte_false as
(
select d.year,
d.week,
count(1) as countFalse
from data d
where d.flag = 0
group by d.year,
d.week
),
cte_true as
(
select d.year,
d.week,
count(1) as countTrue
from data d
where d.flag = 1
group by d.year,
d.week
)
select coalesce(f.year, t.year) as year,
coalesce(f.week, t.week) as week,
f.countFalse,
t.countTrue
from cte_false f
full join cte_true t
on t.year = f.year
and t.week = f.week;
| year | week | countFalse | countTrue |
|---|---|---|---|
| 2021 | 1 | 1 | 2 |
| 2021 | 2 | 4 | null |
| 2021 | 3 | null | 1 |
-- pivot
with cte_count as
(
select d.year,
d.week,
d.flag,
count(1) as countFlag
from data d
group by d.year,
d.week,
d.flag
)
select piv.year,
piv.week,
piv.[0] as countFalse,
piv.[1] as countTrue
from cte_count cc
pivot (max(cc.countFlag) for cc.flag in ([0], [1])) piv;
| year | week | countFalse | countTrue |
|---|---|---|---|
| 2021 | 1 | 1 | 2 |
| 2021 | 2 | 4 | null |
| 2021 | 3 | null | 1 |