473,326 Members | 2,147 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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?

Expand|Select|Wrap|Line Numbers
  1. (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
  2.  
Dec 21 '10 #1
1 1305
Rabbit
12,516 Expert Mod 8TB
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.
Dec 21 '10 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Shawn Fletcher | last post by:
Hi, I'm trying to work around a bug that our helpdesk software has. When a new issue is created, it cannot automatically default 2 fields to the value of No like we need it to. I have a...
15
by: AMT2K5 | last post by:
Hello folks, I seem to have recieved a segfault within my function but am unsure on how to resolve it. I understand that it means that somewhere something is trying to access memory that is not...
0
by: David Elliott | last post by:
I am trying to group data. I have a daily record that contains multiple projects and multiple items in the project. I would like to roll the daily data into project 1 and all the items that go...
1
by: Dalan | last post by:
I have tried both methods of using DSum and creating a Function to address summing some number columns, but to no avail. Since this has been a popular topic over the years, I'm sure I'll receive...
1
by: Dalan | last post by:
I'm experiencing a Query Syntax Error with an Access 97 Db. Actually, the query performs as expected when adding any new records or editing existing ones and even deleting records, EXCEPT when the...
2
by: CSDunn | last post by:
Hello, I need some assistance with error handling in an Access 2003 Project form. The project is has a data source connection to a SQL Server 2000 database. The main form is named...
0
by: Luis Esteban Valencia | last post by:
I've never worked with LDAP before, and I'm having issues connecting to our AD for user authentication. I am trying to use the code found at: ...
0
by: Joe Ross | last post by:
(Apologies in advance if there is a better forum for asking advice on this topic). Our ASP.NET application occasionally starts spitting out OutOfMemory exceptions. When this happens, the memory...
0
by: mwalk66 | last post by:
I have been able to create a web based company phone book by retrieving the last name, first name, email address and telephone # from active directory. However I need serious assistance in...
1
by: AMDRIT | last post by:
Hi gang, this is pretty critical to me. Any help you can provide is appreciated. I have a WebBrowser control placed on a form. I am not getting the same results as I am in IE. Here is the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.