Combine UNION results

[Twitch]

Hello Everyone,

 

I'm hoping there is a simple solution to my issue.  I have the following UNION query:

 

SELECT reservation_items.productQuantity AS PRODUCT_TOTAL, 
products.productLabel,products.productImage
FROM reservations INNER JOIN reservation_items ON reservations.reservationID = reservation_items.reservationID
 INNER JOIN venue_products ON (reservation_items.venueProductID = venue_products.venueProductID) INNER JOIN products ON (venue_products.productID = products.productID)
WHERE reservations.venueID = 84 AND productIdentity = 1 AND reservation_items.venueProductID > 0 AND reservations.reservationStatus != 4 AND YEARWEEK(reservationDate, 1) = YEARWEEK(CURDATE(), 1)
UNION
SELECT SUM(
	productQuantity * specialQuantity
) AS SPECIALS_QUANTITY, 
products.productLabel,products.productImage
FROM reservations INNER JOIN reservation_items ON reservations.reservationID = reservation_items.reservationID
 INNER JOIN special_items ON reservation_items.specialProductID = special_items.specialProductID
 INNER JOIN venue_products ON (special_items.venueProductID = venue_products.venueProductID) INNER JOIN products ON (venue_products.productID = products.productID)
WHERE reservations.venueID = 84
AND productIdentity = 1
AND reservation_items.specialProductID > 0 AND reservations.reservationStatus != 4 AND YEARWEEK(reservationDate, 1) = YEARWEEK(CURDATE(), 1)
GROUP BY products.productID ORDER BY PRODUCT_TOTAL DESC LIMIT 5

 

The query works fine except if say for instance productID 25 is in the first SELECT result and in the second SELECT result it does not combine the quantity into one result.  It will treat them separate.  How can I have this query add the sums of like productIDs?  I'm guessing I can use an IF statement but don't have must experience with those in a MySQL statement.

 

Hope that makes sense.  As always, thanks in advance for the help.

 

-Twitch

[Barand]

example

 

SELECT  x.id , SUM(x.total')
FROM 
(
    SELECT id, amount as total FROM A
    UNION ALL
    SELECT id, SUM(qty) as total FROM B
) as x
GROUP BY id

[Twitch]

Thanks so much for the response Barand.  I will try it out when I get a chance and let you know.

 

-Twitch

[Twitch]

Thanks again Barand...it almost does what I need but it will come in very handy for future queries as well.