Tom Crimmins wrote:
On Friday, April 01, 2005 19:27, Chris wrote:
Hi all,
I've got 3 or 4 queries UNIONed together in a single query. I want to GROUP the UNIONed result, by one field, and SUM() another field.
Is that possible in one query, or will I need to use temporary table and group it from that?
Thanks,
Chris
I don't think you can tacka group by directly on the end of a union.
Try something like this:
select groupcol, sum(sumcol) from (select col1 as groupcol, col2 as sumcol from table1 UNION select col1 as groupcol, col2 as sumcol from table2) as tmptable group by groupcol;
Obviously you can add in your where clause. This is messy but it should work. This is basically using a temp table without 2-stepping it. If you look at the explain it will say using temporary.
Oh, that's cool. I didn't know you could do that. Thanks.
The power of the sub-query never ceases to amaze me. Don't know how I lived without it....
Chris
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
