Re: Generating subtotal reports direct from SQL

On Oct 9, 2007, at 4:53 PM, Owen Hartnett wrote:

>
> I'm hoping there's a real easy way of doing this that I'm just
> missing:
>
> Given a Select statement such as:
>
> Select ID, code, amount from foo where code < 10;
>
> that gives me a table like this:
>
> ID code amount
> _____________________________________
> 1 4 20
> 2 3 10
> 3 4 15
> 4 2 10
> 5 3 9
> 6 3 8
>
> I want to generate a report table like the following (group by code):
>
> ID code amount
> _____________________________________
> 4 2 10
> 2 10
> 2 3 10
> 5 3 9
> 6 3 8
> 3 27
> 1 4 20
> 3 4 15
> 4 35
> 72
>
> 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?

Try generating them and merging them in one queryt:

SELECT ID, code, amount
FROM (SELECT ID, code, amount
FROM table_name
UNION
SELECT null, code, sum(amount)
FROM table_name
GROUP BY code) t
ORDER BY code, test1_id

Note that I didn't test that

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com