Re: caching subtotals: update vs sum -- aaugh!

subtotalling child records into a parent field -- HOWTO?

> --- will trillich <will@serensoft.com> wrote:
> >     create table invoice (
> >         id serial,
> >         bal numeric(8,2)
> >         cost numeric(8,2),
> >         charge numeric(8,2),
> >     );
> >     create table line_item (
> >         id serial,
> >         item_id integer references invoice ( id ),
> >         bal numeric(8,2)
> >         cost numeric(8,2),
> >         charge numeric(8,2),
> >     );

> > i can't seem to get by cerebellum around how to
> > flow all the
> > subtotals upstream -- these don't work:
> >
> >     -- all totals wind up in ONE invoice record
> >     update invoice set
> >       bal = sum(line_item.bal),
> >       cost = sum(line_item.cost),
> >       charge = sum(line_item.charge)
> >     where line_item.item_id = INVOICE.id;
> >
> >     -- syntax error at 'group'
> >     update invoice set
> >       bal = sum(line_item.bal),
> >       cost = sum(line_item.cost),
> >       charge = sum(line_item.charge)
> >     from line_item
> >     where line_item.item_id = INVOICE.id GROUP BY
> > line_item.item_id;

On Thu, Jan 10, 2002 at 05:06:01AM -0800, Andrew Gould wrote:
> Where does 'set.id' come from?  If this was a typo,
> was it in the sql query you executed?  I would think
> that your first attempt should have worked if 'set.id'
> was replaced with 'invoice.id'.

yes, that was a typo (which i fixed in my quoted sql above). and
no, it doesn't do what i want.  (it DOES if i have just ONE
single invoice record in the table -- all totals wind up in just
one invoice record.) if you have an example that DOES work i'd
love to see it!

i'm guessing that this isn't really an obscure task
(subtotalling child records into a parent field) but getting it
to work just ain't happenin' fer me yet. i'm SURE there's gotta
be an sql-friendly way to do this... ?

--
DEBIAN NEWBIE TIP #119 from Jonathan D. Proulx <jon@ai.mit.edu>
:
Having trouble RUNNING REMOTE X APPLICATIONS?  You've tried "xhost
+<host>", set the DISPLAY variable on the remote session, and
checked that the "-nolisten tcp" flag is *not* being sent at X
startup, right?
  Verify that X is really listening: "netstat -tl" will show
all listening tcp ports; you should see port 6000 open if
display :0 is listening (6001 for :1 etc.)
  If it is listening, I'd start wondering about packet filtering
rules. Check ipchains or iptables...

Also see http://newbieDoc.sourceForge.net/ ...