Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-14
8
Medium Priority
?
304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
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 Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

715 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