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@myemma.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