From: Ragnar <gnari@hive.is>
To: pgsql-performance@postgresql.org
Subject: Re: column totals
Date: Fri, 26 May 2006 16:23:37 +0000
Message-ID: <1148660617.8566.51.camel@localhost.localdomain> (raw)
In-Reply-To: <4476D0D7.1080006@silversphere.co.za>
References: <4476D0D7.1080006@silversphere.co.za>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gnari@hive.is
Subject: Re: column totals
In-Reply-To: <1148660617.8566.51.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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