Re: caching subtotals: update vs sum -- aaugh! - Mailing list pgsql-general

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@serensoft.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@yola.bmrb.wisc.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/