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
-
1 1305
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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:
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
| |