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

Will,

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

Best of luck,

Andrew Gould

--- will trillich <will(at)serensoft(dot)com> wrote:
> to save a few cycles, i'm hoping to cache subtotals
> of
> subsidiary records into the corresponding parent
> records -- but
> i can't figure out how to update the parent table
> with the sums
> of the child table fields:
>
> 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),
> );
>
> selecting the sums is easy:
>
> select
> item_id,
> sum(bal) as bal,
> sum(cost) as cost,
> sum(charge) as charge
> from
> line_item
> group by
> item_id;
>
> but 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 = set.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 = set.id GROUP BY
> line_item.item_id;
>
> the next one works, but not even within earshot of
> 'elegant'.
> there's GOTTA be a slicker way, right?
>
> -- ridiculosity of redundancy but it limps into the
> right result
> update invoice set
> bal = (select sum(line_item.bal) where item_id =
> invoice.id),
> cost = (select sum(line_item.cost) where item_id
> = invoice.id),
> charge = (select sum(line_item.charge) where
> item_id = invoice.id)
> from line_item
> where line_item.item_id = set.id GROUP BY
> line_item.item_id;
>
> or should i settle for something like
>
> select
> item_id,
> sum(bal) as bal,
> sum(cost) as cost,
> sum(charge) as charge
> into
> fooey
> from
> line_item
> group by
> item_id
> ;
> update invoice set
> bal = fooey.bal,
> cost = fooey.cost,
> charge = fooey.charge
> where fooey.item_id = id
> ;
> drop table fooey
> ;
>
> ...? seems a bit of the old "long-way-around"...
> surely there's
> a way--
>
> --
> DEBIAN NEWBIE TIP #110 from Dimitri Maziuk
> <dmaziuk(at)yola(dot)bmrb(dot)wisc(dot)edu>
> :
> Here's how to TUNNEL SECURE X11 CONNECTIONS THROUGH
> SSH: on the
> client, do this:
> client# export DISPLAY=client:0.0
> client# ssh -X server
> then once you're logged in at the server, do:
> server# netscape &
> The environment created at the server will include
> the DISPLAY
> variable, so netscape (or whatever) will dialogue
> with the
> client machine. (See "man ssh" for more.)
>
> Also see http://newbieDoc.sourceForge.net/ ...
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/