Solved

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

Posted on 2011-02-14
8
298 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
 

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL query to show different levels from a table. 5 56
Multilanguage Database Design in MySQL 5 69
MySQL ERROR 1045 (28000) 2 62
update joined tables 2 26
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

932 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now