Solved

How do I create a query to combine these other queries into one?

Posted on 2011-02-14
8
300 Views
Last Modified: 2012-05-11
Hey all,

I'm still a novice at writing queries, and I was looking for a little help on something that is a little too complex for me to fully figure out at this point.

Basically what I am trying to do currently involves 4 queries, however in Report Builder, you can only use one dataset on a chart, so I need to combine these quereies into one so that I can get the graph I'm looking for.

First query:
SELECT
***.ProductPartNumber,
'YTD' AS Mo,
Avg(***.daystorework) AS AvgOfElapsedDays

FROM
(***.ProductpartnumberList
INNER JOIN ***.ShopOrderList
ON ***.ProductPartNumber = ***.ProductPartNumber)
INNER JOIN ***.tblReworkRecords
ON ***.ShopOrderNumber = ***.ShopOrderNumber

WHERE
((Year(***.Datereworked) = 2010))
AND
***.ProductPartNumber` IN('023251', '024052', '024352')
AND
***.DaysToRework is not null

GROUP BY ***.ProductPartNumber;

Where the result is:
'023251', 'YTD', '1.6344'
'024052', 'YTD', '1.4423'
'024352', 'YTD', '1.8939'


The second query is:

SELECT
***.ProductPartNumber,
Month(`Datereworked`) AS Mo,
Avg(***.daystorework) AS AvgOfElapsedDays

FROM
(***.ProductpartnumberList
INNER JOIN ***.ShopOrderList
ON ***.ProductPartNumber = ***.ProductPartNumber)
INNER JOIN ***.tblReworkRecords
ON ***.ShopOrderNumber = ***.ShopOrderNumber

WHERE
((year(***.Datereworked)= 2010))
AND
***.productpartnumber IN('023251', '024052', '024352')
AND ***.DaysToRework is not null

GROUP BY ***.ProductPartNumber, Month(`Datereworked`);

Where the result is:
'023251', '12', '1.6344'
'024052', '9', '0.1429'
'024052', '10', '0.8889'
'024052', '12', '4.6667'
'024352', '9', '0.2222'
'024352', '10', '2.0303'
'024352', '12', '2.3333'
(these particular parts were only produced in the months above, others would be year-round, 1-12)


Now what I am looking for, is an end result that looks something like:

'023251', '12', '1.6344'
'023251', 'YTD', '1.6344'
'024052', '9', '0.1429'
'024052', '10', '0.8889'
'024052', '12', '4.6667'
'024052', 'YTD', '1.4423'
'024352', '9', '0.2222'
'024352', '10', '2.0303'
'024352', '12', '2.3333'
'024352', 'YTD', '1.8939'

So that I can plot these on one chart, with YTD being the last value (a line graph that charts the entire year, with YTD being the final, right-most value in the chart).


The other two queries are basically the same thing, except they combine the data for all parts.
Eventually, I want "All Parts" to also be on the same chart, but to avoid making this post become too long and complicated, I'll just focus on figuring this part out for now.

THANK YOU in advance for any help!!!
Very much appreciated!


0
Comment
Question by:AAI_Development
  • 4
  • 4
8 Comments
 
LVL 9

Expert Comment

by:wellhole
ID: 34888673
You just have to slap a UNION ALL between the 2 queries and add an order by at the end for ***.ProductPartNumber, Mo, AvgOfElapsedDays.
0
 

Author Comment

by:AAI_Development
ID: 34888783
I had previously tried that, but with Group By instead of Order By (didn't give me the intended result)...tried Order By instead and:
 Error Code: 1054
Unknown column '***.Product Part Number' in 'order clause'
0
 
LVL 9

Expert Comment

by:wellhole
ID: 34888801
You're going to need to keep the group by in there if you want the same results from both queries. As for your error, I'd have to say its obvious that *** is not a table name especially since you blocked it out with *** yourself.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:AAI_Development
ID: 34888882
I'm putting the table name in where *** is...

anyways I ran:
SELECT
***.ProductPartNumber,
Month(`Datereworked`) AS Mo,
Avg(***.daystorework) AS AvgOfElapsedDays

FROM
(***.ProductpartnumberList
INNER JOIN ***.ShopOrderList
ON ***.ProductPartNumber = ***.ProductPartNumber)
INNER JOIN ***.tblReworkRecords
ON ***.ShopOrderNumber = ***.ShopOrderNumber

WHERE
((Year(***.Datereworked) = 2010))
AND
***.ProductPartNumber` IN('023251', '024052', '024352')
AND
***.DaysToRework is not null

UNION ALL

SELECT
***.ProductPartNumber,
YTD AS Mo,
Avg(***.daystorework) AS AvgOfElapsedDays

FROM
(***.ProductpartnumberList
INNER JOIN ***.ShopOrderList
ON ***.ProductPartNumber = ***.ProductPartNumber)
INNER JOIN ***.tblReworkRecords
ON ***.ShopOrderNumber = ***.ShopOrderNumber

WHERE
((year(***.Datereworked)= 2010))
AND
***.productpartnumber IN('023251', '024052', '024352')
AND ***.DaysToRework is not null

ORDER BY `Product Part Number`, Mo, AvgOfElapsedDays

and got:
'024052', *BLOB*, '1.6579'
'024052', *BLOB*, '1.6579'

...not what I'm looking for yet
0
 
LVL 9

Accepted Solution

by:
wellhole earned 500 total points
ID: 34888947
Please change your query back to the original posted query. What you have there is not the same (truly it isn't) and thats why its not producing what you want. Just do these 2 things:

1) Put UNION ALL between the 2 queries.
2) Put ORDER BY ProductPartNumber, Mo, AvgOfElapsedDays at the end.
0
 

Author Comment

by:AAI_Development
ID: 34889030
You're right, I left out the GROUP BY in each, once I put that in, I got:

'023251', ?, '1.6344'
'023251', ?, '1.6344'
'024052', ?, '0.8889'
'024052', ?, '4.6667'
'024052', ?, '0.1429'
'024052', ?, '1.4423'
'024352', ?, '2.0303'
'024352', ?, '2.3333'
'024352', ?, '0.2222'
'024352', ?, '1.8939'

Thank you, I am almost there, I just now need to fix the 2nd column.  All of those '?'s are BLOB in MySQL...any way to fix that?
0
 
LVL 9

Assisted Solution

by:wellhole
wellhole earned 500 total points
ID: 34889057
The unioned queries must have the same column types. I see that you have 'YTD' originally, but YTD in the later comment. If that's correct, you gotta put that in the quotes. I also see Month(`Datereworked`) as the unioned column will result in a number. Please cast that as a string type -- cast(Month(`Datereworked`) as char)
0
 

Author Comment

by:AAI_Development
ID: 34889144
AHH BEAUTIFUL

Thank you very much.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to count in a table in php 22 45
MySQL Warning Statements when you have a LIMIT clause. 6 39
Creating Functions in phpMyAdmin 8 26
Complex SQL statement in VB.NET 7 31
Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question