Link to home
Create AccountLog in
Avatar of AAI_Development
AAI_Development

asked on

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

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!


Avatar of wellhole
wellhole

You just have to slap a UNION ALL between the 2 queries and add an order by at the end for ***.ProductPartNumber, Mo, AvgOfElapsedDays.
Avatar of AAI_Development

ASKER

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'
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.
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
ASKER CERTIFIED SOLUTION
Avatar of wellhole
wellhole

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
AHH BEAUTIFUL

Thank you very much.