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@xxxxxxxxxx
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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
