This query gets the total sales of each item

SELECT Item_T.INAME, SUM(Sales_T.SALEQTY) AS TOTAL_SOLD
    FROM Item_T
    LEFT JOIN Sales_T
        ON Item_T.INAME = Sales_T.INAME
    GROUP BY Item_T.INAME;

This query gets the total number delivered for each item

SELECT Item_T.INAME, SUM(Delivery_T.DQTY) AS TOTAL_DELIVERED
    FROM Item_T
    LEFT JOIN Delivery_T
        ON Item_T.INAME = Delivery_T.INAME
    GROUP BY Item_T.INAME;

The above two queries give the correct output individually, but when combined like I did below the output is off specifically values where either TOTAL_SOLD or TOTAL_DELIVERED isn't null or where there are multiple occurences of deliveries or sales for the item.

SELECT Item_T.INAME, SUM(Sales_T.SALEQTY) AS TOTAL_SOLD, SUM(Delivery_T.DQTY) AS TOTAL_DELIVERED
    FROM Item_T
    LEFT JOIN Delivery_T
        ON Item_T.INAME = Delivery_T.INAME
    LEFT JOIN Sales_T
        ON Item_T.INAME = Sales_T.INAME
    GROUP BY Item_T.INAME;

What would be an effective way to combine these to queries so that the output matches what the first 2 queries produce individually?

edit: schema is shown here:

CodePudding user response：

You can move one of the agregations to a sub-query to avoid making a cartesian join.

SELECT 
  Item_T.INAME, 
  SUM(Sales_T.SALEQTY) AS TOTAL_SOLD, 
  Delivery.TOTAL_DELIVERED
FROM Item_T
LEFT JOIN ( 
  SELECT
    INAME,      
    SUM(Delivery_T.DQTY) AS TOTAL_DELIVERED,
  FROM Delivery_T
  GROUP BY INAME
  ) AS Delivery
  ON Item_T.INAME = Del.INAME
LEFT JOIN Sales_T
  ON Item_T.INAME = Sales_T.INAME
GROUP BY 
  Item_T.INAME,
  TOTAL_DELIVERED;

CodePudding user response：

You are joining all sales and all deliveries to an item. The problem with this: this combines the individual sales with the deliveries, although these are not related. Say you got the items in two deliveries and you have four sales so far, then your join gets you eight rows (each delivery combined with each sale). E.g.:

Deliveries

sales

Joined result

If you now add up the quantities in the rows, you'll get incorrect results, because the original quantities get multiplied with the number of rows for the item in the other table.

What you want instead is join the delivery quantity and the sales quantity to the item:

select i.iname, s.total_sold, d.total_delivered
from item_t i
left join 
(
  select iname, sum(dqty) as total_delivered
  from delivery_t
  group by iname
) d on d.iname = i.iname
left join 
(
  select iname, sum(saleqty) as total_sold
  from sales_t
  group by iname
) s on s.iname = i.iname
order by i.iname;