> Date: Sun, 28 Feb 1999 10:46:56 +0200 (SAST)
> From: Anton de Wet <adw@obsidian.co.za>
> Subject: Update questions: grouping, null values
>
> Hi
>
> This is a simplified example of a problem I'm having, the reason for
> picking the tables as I have is required for the real setting, here it
> looks slightly artificial.
>
> Say I have 2 tables:
> create table daydata ( date date, site text, nr int4, charge char);
> create table summary ( date date, total_y int4, total_n int4);
[snip]
> I would like to create entries in the summary table that has the totals
> per day of the entries marked 'y' and 'n' in the daydata table.
> The daydata can be added to (or y modified to n) and I have to run an
> update to recreate the summary table.
here's my (untested) attempt...
create view summary_y as
select date, sum(nr) as sum_y from daydata
where charge = 'y' group by date;
create view summary_n as
select date, sum(nr) as sum_n from daydata
where charge = 'n' group by date;
insert into summary
select y.date, count_y, count_n
from summary_y as y, symmary_n as n
where y.date = n.date;
> To initially create the summary dates:
> insert into summary (date) select distinct date from daydata;
>
>
> Then what I'd like to do is something like:
> update summary set total_y=sum(nr) from daydata
> where daydata.date=summary.date and charge='y';
I think since you are using an aggregate without a group by clause (which
you can't use anyway in an update), it's aggregating the whole thing,
which makes total_y the total of all the y's, and daydata.date,
daydata.nr, and so on the values for the last one in the selected group.
I'm not entirely certain, however.
[snip]
> Idealy I'd like a single SQL statment like the one I had in mind above to
> do it.
I can't think of a single statement to do it without nesting in the from
clause... the solution I gave above can be reduced to two at the cost of
readability.
best of luck,
andru
