Bonnie Zahm Bonnie Zahm - 11 days ago 7
MySQL Question

UNION ALL query missing data?

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

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.