UNION ALL query missing data?

Question

Using UNION ALL to get results from two queries but it is losing a row of data. If I switch which select statement comes first the missing data switches to a different row. I know each slect statement pulls correct information when ran seperate but when put together not working correctly. The following is my query:

SELECT YEAR( orderDate ) AS "SalesYear"
     , MONTH( orderDate ) AS "SalesMonth"
     , SUM( orderTotal ) AS "TotalSales"
     , orderDetails.productID 
FROM orders
INNER JOIN orderdetails 
   ON orders.orderID = orderDetails.orderID
INNER JOIN products
   ON orderDetails.productID = products.productID

UNION ALL
SELECT YEAR( orderDate ) AS "SalesYear"
     , MONTH( orderDate ) AS "SalesMonth"
     , SUM( orderTotal ) AS "TotalSales"
     , instorepurchasedetails.productID 
FROM in_storepurchase
INNER JOIN instorepurchasedetails 
   ON in_storepurchase.isPurchaseID = instorepurchasedetails.isPurchaseID
INNER JOIN products
   ON instorepurchasedetails.productID = products.productID
GROUP BY YEAR(orderDate ), MONTH( orderDate ), orderTotal, productID

Any ideas why this is happening and what I can do?

Answer 1

Your need to add a GROUP BY clause to each SELECT block, and specify the correct columns, something like this:

SELECT "SalesYear"
     , "SalesMonth"
     , SUM( "TotalSales" ) AS "TotalSales"
     , productID 
FROM (
   SELECT YEAR( orderDate ) AS "SalesYear"
        , MONTH( orderDate ) AS "SalesMonth"
        , SUM( orderTotal ) AS "TotalSales"
        , orderDetails.productID 
   FROM orders
   INNER JOIN orderdetails 
      ON orders.orderID = orderDetails.orderID
   INNER JOIN products
      ON orderDetails.productID = products.productID
   GROUP BY YEAR(orderDate ), MONTH( orderDate ), orderDetails.productID

   UNION ALL
   SELECT YEAR( orderDate ) AS "SalesYear"
        , MONTH( orderDate ) AS "SalesMonth"
        , SUM( orderTotal ) AS "TotalSales"
        , instorepurchasedetails.productID 
   FROM in_storepurchase
   INNER JOIN instorepurchasedetails 
      ON in_storepurchase.isPurchaseID = instorepurchasedetails.isPurchaseID
   INNER JOIN products
      ON instorepurchasedetails.productID = products.productID
   GROUP BY YEAR(orderDate), MONTH(orderDate), instorepurchasedetails.productID
   ) x
GROUP BY "SalesYear", "SalesMonth", productID

I also removed the orderTotal from the GROUP BY clause because I don't think that's what you needed.

EDIT: Updated based on OP comment.

Answer 2

SELECT `x`.`SalesYear`, 
`x`.`SalesMonth`, 
SUM( `x`. `TotalSales`) AS "TotalSales", 
productID

FROM
(

     SELECT YEAR( orderDate ) AS "SalesYear", 
     MONTH( orderDate ) AS "SalesMonth", 
     SUM( orderTotal ) AS "TotalSales", 
     orderDetails.productID 
    FROM orders
    INNER JOIN orderdetails ON orders.orderID = orderDetails.orderID
    INNER JOIN products ON orderDetails.productID = products.productID
    GROUP BY YEAR(orderDate ) , MONTH( orderDate ), productID 

UNION ALL

     SELECT YEAR( orderDate ) AS "SalesYear", 
     MONTH( orderDate ) AS "SalesMonth", 
     SUM( orderTotal ) AS "TotalSales", 
     instorepurchasedetails.productID 
    FROM in_storepurchase
    INNER JOIN instorepurchasedetails ON in_storepurchase.isPurchaseID =                       instorepurchasedetails.isPurchaseID
    INNER JOIN products ON instorepurchasedetails.productID = products.productID
    GROUP BY YEAR(orderDate ) , MONTH( orderDate ), productID 
) x
     GROUP BY  `x`.`SalesMonth`, productID 
     ORDER BY `x`.`SalesMonth` ASC