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




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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
>


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings