Re: cross tab needs in SQL
You are doing more work than you need to with this table structure. The
table is already grouped by id for each different day in your
representation so there is no reason to do a sum or group by.
You only have to do the following:
select id, cnt day_1, 0 day_4, 0 day_20, 0 day_25
from pru where days = 1
into temp resu;
insert into resu
select id, 0, cnt, 0, 0 from pru where days = 4;
insert into resu
select id, 0, 0, cnt, 0 from pru where days = 20;
insert into resu
select id, 0, 0, 0, cnt from pru where days = 25;
select id, sum(day_1) day_1,
sum(day_4) day_4,
sum(day_20) day_20,
sum(day_25) day_25
from resu
group by 1
order by 1;
Notice that you only have to group in the last query which is
essentially putting the id records together.
with this table structure my SQL would look like:
select
t.id,
sum( case days
when 1 then cnt
else 0
end
) day1,
sum( case days
when 4 then cnt
else 0
end
) day2,
sum( case days
when 20 then cnt
else 0
end
) day3,
sum( case days
when 25 then cnt
else 0
end
) day4
from
pru t
group by
id
order by id
;
Notice the use of cnt instead of 1 in the then clause. In my earlier
example 1 stands for the fact that you found 1 record that matched the
criteria, when you sum them you have the number of records that you
found. In this example you return cnt which is the number of days of
this type for this ID.