I have 3 tables: Orders, Returns, Region

I'm trying to sum the total sales and returns and group them by region. I'm trying to join the third table of returns, to only display the sum of the sales where there is a OrderID in the return table matching an OrderID from the Order table and grouped by RegionID, but I'm having trouble figuring it out.

I have it working for total sales in a region using:

SELECT r.Region, SUM(o.sales)
FROM Orders o
INNER JOIN Region r ON o.RegionID = r.RegionID
GROUP BY o.RegionID

Edit:

Final result should look like:

CodePudding user response：

Try this:

SELECT
    r.RegionID
    , SUM(o.sales) [Total Sales]
    , SUM(CASE WHEN rt.orderId IS NOT NULL THEN o.sales ELSE 0 END) [Total Returns]
FROM Orders o
INNER JOIN Region r ON o.RegionID = r.RegionID
LEFT JOIN returns rt ON rt.orderId=o.orderId
GROUP BY o.RegionIDvalues

CodePudding user response：

Here you can use an inner join to join your Region table. This will only take into consideration the orders with a return since you are not using a OUTER JOIN. You then do your SUM the same as before. This will allow you to have the Total Returns. For the total of sales, my solution would be to do a sub query to select all of the sales, with or without a return.

SELECT REG.RegionID, SUM(O.SALES) AS Total_Sales, SUM(
    SELECT O.SALES 
    FROM ORDER O
    WHERE O.RegionID = REG.RegionID) AS Total_Returns
FROM ORDERS O
INNER JOIN REGION REG
    ON O.RegionID = REG.RegionID
INNER JOIN RETURNS RET
    ON O.OrderID = RET.OrderID
GROUP BY REG.RegionID