Ayman Ayman - 4 months ago 9
SQL Question

Aggregate function returns inaccurate results

Hello i am trying to get item balance

where

balance = SUM(pod.Quantity) + SUM(iob.OpeiningBalance)


The below query gives me the following out put

ItemName Balance
Item1 35


it supposed to be

ItemName Balance
Item1 25


Query

SELECT
i.ItemName,
SUM(pod.Quantity) + SUM(iob.OpeiningBalance) AS Balance
FROM dbo.Items i
left JOIN dbo.PurchaseOrderDetails pod
ON i.ItemID = pod.ItemID
left JOIN ItemOpeningBalances iob
ON i.ItemID = iob.ItemID
GROUP BY i.ItemName


Items Table

+--------+----------+
| ItemID | ItemName |
+--------+----------+
| 1 | Item1 |
+--------+----------+


ItemOpeningBalances Table

+-------------------+-----------------+-----------------+
| OpeiningBalanceID | ItemID | OpeiningBalance |
+-------------------+-----------------+-----------------+
| 1 | 1 | 10 |
+-------------------+-----------------+-----------------+


PurchaseOrderDetails Table

+-----------------------+--------+----------+
| PurchaseOrderDetailID | ItemID | Quantity |
+-----------------------+--------+----------+
| 1 | Item1 | 5 |
| 2 | Item1 | 10 |
+-----------------------+--------+----------+

Answer

you query are adding up the opening balance for every lines of PurchaseOrderDetails.

Change to using sub query on the PurchaseOrderDetails

SELECT 
  i.ItemName,
  --SUM(pod.Quantity) + SUM(iob.OpeiningBalance) AS Balance
  (pod.Quantity) + (iob.OpeiningBalance) AS Balance
FROM dbo.Items i
left JOIN 
( -- change to sum it in the sub query
  select ItemId, Quantity  = sum(Quantity)
  from dbo.PurchaseOrderDetails 
  group by ItemId
) pod
  ON i.ItemID = pod.ItemID
  left JOIN ItemOpeningBalances iob
  ON i.ItemID = iob.ItemID
--GROUP BY i.ItemName,
--         i.ItemCode