Re: Generating subtotal reports direct from SQL - Mailing list pgsql-general

"Owen Hartnett" <owen@clipboardinc.com> writes:

> Such that the final table has additional subtotal rows with the aggregate sum
> of the amounts.  I'm thinking I can generate two tables and merge them, but is
> there an easier way using a fancy Select statement?

Unfortunately the fancy SQL feature you're looking for is ROLLUP which
Postgres doesn't support.

I think you would have to do

(
select id, code, amount from foo where code < 10
union all
select null, code, sum(amount) from foo where code < 10 group by code
) order by code, id


--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com