Skip site navigation (1) Skip section navigation (2)

Re: column totals

From: Ragnar <gnari(at)hive(dot)is>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: column totals
Date: 2006-05-26 16:23:37
Message-ID: 1148660617.8566.51.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: 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



In response to

pgsql-performance by date

Next:From: Merlin MoncureDate: 2006-05-26 16:56:44
Subject: Re: is it possible to make this faster?
Previous:From: Tom LaneDate: 2006-05-26 14:22:38
Subject: Re: is it possible to make this faster?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group