Update questions: grouping, null values - Mailing list pgsql-sql

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);

and some data:
insert into daydata values ( '1-2-1999', 'XXX', 3, 'y');
insert into daydata values ( '1-2-1999', 'YYY', 2, 'y');
insert into daydata values ( '1-2-1999', 'XXX', 5, 'n');
insert into daydata values ( '2-2-1999', 'XXX', 1, 'y');
insert into daydata values ( '3-2-1999', 'YYY', 4, 'n');
insert into daydata values ( '4-2-1999', 'XXX', 3, 'y');
insert into daydata values ( '4-2-1999', 'XXX', 2, 'y');


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.


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';


But that gives:
test=> select * from summary;
      date|total_y|total_n
----------+-------+-------
02-02-1999|       |
03-02-1999|       |
04-02-1999|       |
01-02-1999|     11|
(4 rows)

Not exactly what I had in mind.

As I'm executing it from tcl I though, no problem, for this application it
will be acceptable to do it date by date, so I do something like:

foreach date [lsql "select distinct date from summary"] {
    fsql "update summary set total_y=sum(nr) from daydata
        where daydata.date='$date' and summary.date='$date and charge='y';
}

Now this works fine for the dates which have data, but days without data
for either y (in this case) gets:

update summary set total_y=sum(nr) from daydata where
daydata.date='3-2-1999' and summary.date='3-2-1999' and
daydata.charge='y';

ERROR:  ExecutePlan: (junk) `ctid' is NULL!

Now I would be quite happy if it just set total_y to either Null or '0' in
this case, but its becoming a bit much to do the select for each of these
cases, test for null externally and only update if its not null.

Idealy I'd like a single SQL statment like the one I had in mind above to
do it.

Any ideas?

Anton


DNA is God's contribution to the Open Source movement