Need to combine and get results-CodePudding

I'm trying to do the following:

I want to minus the amount of RMAs from Sales:

This is the query I did for Sales:

SELECT COUNT(*) AS PRODUCT_SALES_NUMBER, Orders.SKU AS PRODUCT_SKU, Orders.Description AS PRODUCT_DESCRIPTION
FROM Orders
GROUP BY PRODUCT_SKU
ORDER BY PRODUCT_SALES_NUMBER DESC 
LIMIT 9;

This is the one I did for RMAs:

SELECT COUNT(*) AS RETURNED_AMOUNT, Orders.SKU AS PRODUCT_SKU, Orders.Description AS PRODUCT_DESCRIPTION
FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID
WHERE UPPER(Status) = "COMPLETE"
GROUP BY PRODUCT_SKU
ORDER BY RETURNED_AMOUNT DESC
LIMIT 9;

I would like to take the results (let's say 5000 sales and 3000 rma) and return a formatted result.

Can you please help? Thanks!!

CodePudding user response：

You can jojn both subqueries, only the second can't contain an ORDER VY or LIMIT, because teh first subquery defines the PRODUCT_SKU that are choosen to be in the result set.

SELECT
PRODUCT_SALES_NUMBER - RETURNED_AMOUNT, ord1.PRODUCT_SKU, ord1.PRODUCT_DESCRIPTION
FROM
(SELECT COUNT(*) AS PRODUCT_SALES_NUMBER, Orders.SKU AS PRODUCT_SKU, Orders.Description AS PRODUCT_DESCRIPTION
FROM Orders
GROUP BY PRODUCT_SKU,PRODUCT_DESCRIPTION
ORDER BY PRODUCT_SALES_NUMBER DESC 
LIMIT 9) ord1 JOIN (
SELECT COUNT(*) AS RETURNED_AMOUNT, Orders.SKU AS PRODUCT_SKU, Orders.Description AS PRODUCT_DESCRIPTION
FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID
WHERE UPPER(Status) = "COMPLETE"
GROUP BY PRODUCT_SKU,PRODUCT_DESCRIPTION) ord2 ON ord1.PRODUCT_SKU = ord2.PRODUCT_SKU

CodePudding user response：

To get the numbers in DESC order, I added the following line to the bottom: ORDER BY PRODUCT_SALES_NUMBER - RETURNED_AMOUNT DESC;

So the whole script looks like:

SELECT
    PRODUCT_SALES_NUMBER - RETURNED_AMOUNT, ord1.PRODUCT_SKU, ord1.PRODUCT_DESCRIPTION
FROM
    (
        SELECT COUNT(*) AS PRODUCT_SALES_NUMBER, Orders.SKU AS PRODUCT_SKU, Orders.Description AS PRODUCT_DESCRIPTION
        FROM Orders
        GROUP BY PRODUCT_SKU, PRODUCT_DESCRIPTION 
        ORDER BY PRODUCT_SALES_NUMBER DESC
        LIMIT 20
    ) ord1 JOIN (
        SELECT COUNT(*) AS RETURNED_AMOUNT, Orders.SKU AS PRODUCT_SKU, Orders.Description AS PRODUCT_DESCRIPTION
        FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID
        WHERE UPPER(Status) = 'COMPLETE' OR UPPER(Status) = 'Initiated' OR UPPER(Status) = 'Pending'
        GROUP BY PRODUCT_SKU, PRODUCT_DESCRIPTION
    ) ord2 ON ord1.PRODUCT_SKU = ord2.PRODUCT_SKU
ORDER BY PRODUCT_SALES_NUMBER - RETURNED_AMOUNT DESC;