Re: UNION ALL and GROUP BY
Chris [EMAIL PROTECTED] wrote on 04/02/2005 12:35:57 AM: 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 This isn't a subquery, it's a derived table. Similar syntax but very different usage. You would get an error if you DIDN'T alias your derived table. That way the outer query knows what the name of the table is that contains the data that you want to work on. Tom's query is no different than saying: SELECT groupcol, sum(sumcol) FROM tmptable GROUP BY groupcol except that in this case tmptable is the result of a query and not an actual table (or temporary table). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
UNION ALL and GROUP BY
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UNION ALL and GROUP BY
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. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNION ALL and GROUP BY
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]
