pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcolumn totals
3+ messages / 2 participants
[nested] [flat]
* column totals
@ 2006-05-26 09:56 James Neethling <james@silversphere.co.za>
0 siblings, 2 replies; 3+ messages in thread
From: James Neethling @ 2006-05-26 09:56 UTC (permalink / raw)
To: pgsql-performance
Hi There,
I've got a situation where I need to pull profit information by product
category, as well as the totals for each branch.
Basically, something like
SELECT branch_id, prod_cat_id, sum(prod_profit) as prod_cat_profit
FROM () as b1
WHERE x = y
GROUP BY branch, prod_cat_id
Now, I also need the branch total, effectively,
SELECT branch_id, sum(prod_profit) as branch_total
FROM () as b1
WHERE x = y
GROUP BY branch_id.
Since the actual queries for generating prod_profit are non-trivial, how
do I combine them to get the following select list?
Or is there a more efficient way?
Kind Regards,
James
Attachments:
james.vcf (text/x-vcard, 390B) download
begin:vcard
fn:James Neethling
n:Neethling;James
org:Silver Sphere Business Solutions
adr:Centurion Business Park A2;;25633 Democracy Way;Prosperity Park;Milnerton;Cape Town;7441
email;internet:james@silversphere.co.za
title:Managing Member
tel;work:27 21 552 7108
tel;fax:27 21 552 7106
tel;cell:27 83 399 2799
x-mozilla-html:FALSE
url:http://www.silversphere.co.za
version:2.1
end:vcard
* Re: column totals
@ 2006-05-26 13:19 James Neethling <james@silversphere.co.za>
parent: James Neethling <james@silversphere.co.za>
1 sibling, 0 replies; 3+ messages in thread
From: James Neethling @ 2006-05-26 13:19 UTC (permalink / raw)
To: pgsql-performance
James Neethling wrote:
> Hi There,
>
> I've got a situation where I need to pull profit information by
> product category, as well as the totals for each branch.
>
> Basically, something like
>
> SELECT branch_id, prod_cat_id, sum(prod_profit) as prod_cat_profit
> FROM () as b1
> WHERE x = y
> GROUP BY branch, prod_cat_id
>
>
> Now, I also need the branch total, effectively,
> SELECT branch_id, sum(prod_profit) as branch_total
> FROM () as b1
> WHERE x = y
> GROUP BY branch_id.
>
>
> Since the actual queries for generating prod_profit are non-trivial,
> how do I combine them to get the following select list?
SELECT branch_id, prod_cat_id, sum(prod_profit) as prod_cat_profit,
sum(prod_profit) as branch_total
>
> Or is there a more efficient way?
>
> Kind Regards,
> James
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
Attachments:
james.vcf (text/x-vcard, 390B) download
begin:vcard
fn:James Neethling
n:Neethling;James
org:Silver Sphere Business Solutions
adr:Centurion Business Park A2;;25633 Democracy Way;Prosperity Park;Milnerton;Cape Town;7441
email;internet:james@silversphere.co.za
title:Managing Member
tel;work:27 21 552 7108
tel;fax:27 21 552 7106
tel;cell:27 83 399 2799
x-mozilla-html:FALSE
url:http://www.silversphere.co.za
version:2.1
end:vcard
* Re: column totals
@ 2006-05-26 16:23 Ragnar <gnari@hive.is>
parent: James Neethling <james@silversphere.co.za>
1 sibling, 0 replies; 3+ messages in thread
From: Ragnar @ 2006-05-26 16:23 UTC (permalink / raw)
To: pgsql-performance
On fös, 2006-05-26 at 11:56 +0200, James Neethling wrote:
> SELECT branch_id, prod_cat_id, sum(prod_profit) as prod_cat_profit
> FROM () as b1
> WHERE x = y
> GROUP BY branch, prod_cat_id
>
>
> Now, I also need the branch total, effectively,
> SELECT branch_id, sum(prod_profit) as branch_total
> FROM () as b1
> WHERE x = y
> GROUP BY branch_id.
>
>
> Since the actual queries for generating prod_profit are non-trivial, how
> do I combine them to get the following select list?
one simple way using temp table and 2 steps:
CREATE TEMP TABLE foo AS
SELECT branch_id,
prod_cat_id,
sum(prod_profit) as prod_cat_profit
FROM () as b1
WHERE x = y
GROUP BY branch, prod_cat_id;
SELECT branch_id,
prod_cat_id,
prod_cat_profit,
branch_total
FROM foo as foo1
JOIN
(SELECT branch_id,
sum(prod_cat_profit) as branch_total
FROM foo
GROUP BY branch_id
) as foo2 USING branch_id;
(untested)
gnari
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-05-26 16:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-26 09:56 column totals James Neethling <james@silversphere.co.za>
2006-05-26 13:19 ` James Neethling <james@silversphere.co.za>
2006-05-26 16:23 ` Ragnar <gnari@hive.is>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox