I am trying to use UNION AND GROUP BY. Its not working and I need assistance :)

[Brad Blankenshi]

I am creating a query that has multiples unions and a GROUP BY. If I remove the GROUP BY, it works, just doesn't provide the results I'm looking for. Can someone provide assistance to help me get this working with the GROUP BY?

(SELECT "Software" as saletype, DATE_FORMAT(quotes.original_po_date,"%Y-%m") as m, sum(quotes_cstm.softwaretotal_c) as total, count(*) as quote_count FROM quotes LEFT JOIN quotes_cstm quotes_cstm ON quotes.id = quotes_cstm.id_c WHERE quotes.original_po_date >= DATE_FORMAT("{$fcd_date_start}", "%Y-%m-%d %H:%i:%s") AND quotes.original_po_date <= DATE_FORMAT("{$fcd_date_end}", "%Y-%m-%d %H:%i:%s") AND quotes.deleted=0) UNION  (SELECT "Maintenance" as saletype, DATE_FORMAT(quotes.original_po_date,"%Y-%m") as m, sum(quotes_cstm.softwaremaintenancetotal_c) as total, count(*) as quote_count FROM quotes LEFT JOIN quotes_cstm quotes_cstm ON quotes.id = quotes_cstm.id_c WHERE quotes.original_po_date >= DATE_FORMAT("{$fcd_date_start}", "%Y-%m-%d %H:%i:%s") AND quotes.original_po_date <= DATE_FORMAT("{$fcd_date_end}", "%Y-%m-%d %H:%i:%s") AND quotes.deleted=0) UNION (SELECT "Services" as saletype, DATE_FORMAT(quotes.original_po_date,"%Y-%m") as m, sum(quotes_cstm.servicestotal_c) as total, count(*) as quote_count FROM quotes LEFT JOIN quotes_cstm quotes_cstm ON quotes.id = quotes_cstm.id_c WHERE quotes.original_po_date >= DATE_FORMAT("{$fcd_date_start}", "%Y-%m-%d %H:%i:%s") AND quotes.original_po_date <= DATE_FORMAT("{$fcd_date_end}", "%Y-%m-%d %H:%i:%s") AND quotes.deleted=0) UNION (SELECT "SaaS" as saletype, DATE_FORMAT(quotes.original_po_date,"%Y-%m") as m, sum(quotes_cstm.saastotal_c) as total, count(*) as quote_count FROM quotes LEFT JOIN quotes_cstm quotes_cstm ON quotes.id = quotes_cstm.id_c WHERE quotes.original_po_date >= DATE_FORMAT("{$fcd_date_start}", "%Y-%m-%d %H:%i:%s") AND quotes.original_po_date <= DATE_FORMAT("{$fcd_date_end}", "%Y-%m-%d %H:%i:%s") AND quotes.deleted=0) UNION (SELECT "Third Party Software(NET)" as saletype, DATE_FORMAT(quotes.original_po_date,"%Y-%m") as m, sum(quotes_cstm.tp_softwarenet_c) as total, count(*) as quote_count FROM quotes LEFT JOIN quotes_cstm quotes_cstm ON quotes.id = quotes_cstm.id_c WHERE quotes.original_po_date >= DATE_FORMAT("{$fcd_date_start}", "%Y-%m-%d %H:%i:%s") AND quotes.original_po_date <= DATE_FORMAT("{$fcd_date_end}", "%Y-%m-%d %H:%i:%s") AND quotes.deleted=0) UNION (SELECT "Third Party Services(NET)" as saletype, DATE_FORMAT(quotes.original_po_date,"%Y-%m") as m, sum(quotes_cstm.tp_servicesnet_c) as total, count(*) as quote_count FROM quotes LEFT JOIN quotes_cstm quotes_cstm ON quotes.id = quotes_cstm.id_c WHERE quotes.original_po_date >= DATE_FORMAT("{$fcd_date_start}", "%Y-%m-%d %H:%i:%s") AND quotes.original_po_date <= DATE_FORMAT("{$fcd_date_end}", "%Y-%m-%d %H:%i:%s") AND quotes.deleted=0) UNION (SELECT "Third Party Hardware(NET)" as saletype, DATE_FORMAT(quotes.original_po_date,"%Y-%m") as m, sum(quotes_cstm.tp_hardwarenet_c) as total, count(*) as quote_count FROM quotes LEFT JOIN quotes_cstm quotes_cstm ON quotes.id = quotes_cstm.id_c WHERE quotes.original_po_date >= DATE_FORMAT("{$fcd_date_start}", "%Y-%m-%d %H:%i:%s") AND quotes.original_po_date <= DATE_FORMAT("{$fcd_date_end}", "%Y-%m-%d %H:%i:%s") AND quotes.deleted=0) UNION (SELECT "Third Party SaaS(NET)" as saletype, DATE_FORMAT(quotes.original_po_date,"%Y-%m") as m, sum(quotes_cstm.tp_saasnet_c) as total, count(*) as quote_count FROM quotes LEFT JOIN quotes_cstm quotes_cstm ON quotes.id = quotes_cstm.id_c WHERE quotes.original_po_date >= DATE_FORMAT("{$fcd_date_start}", "%Y-%m-%d %H:%i:%s") AND quotes.original_po_date <= DATE_FORMAT("{$fcd_date_end}", "%Y-%m-%d %H:%i:%s") AND quotes.deleted=0) GROUP BY saletype,DATE_FORMAT(m,"%Y-%m") ORDER BY m

[Rabbit]

The group by on the date needs to be within each unioned query. I don't know if MySQL allows for pivots or crosstabs but you should see if it does, it would make it much easier.