caching subtotals: update vs sum -- aaugh!
От | will trillich |
---|---|
Тема | caching subtotals: update vs sum -- aaugh! |
Дата | |
Msg-id | 20020110031933.A26651@serensoft.com обсуждение исходный текст |
Ответы |
Re: caching subtotals: update vs sum -- aaugh!
("Alaric B. Snell" <abs@frontwire.com>)
Re: caching subtotals: update vs sum -- aaugh! (Andrew Gould <andrewgould@yahoo.com>) Re: caching subtotals: update vs sum -- aaugh! (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-general |
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/ ...
В списке pgsql-general по дате отправления: