Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-02-14
8
Medium Priority
?
305 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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 2000 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 2000 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

916 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