Aggregate function SUM returning different values

Question

I have following sample database schema

https://i.sstatic.net/EKNxY.jpg

enter image description here

I want:
A) All sales, group by Users.
B) Discounts, group by users.

I am using following queries:
FOR A:

    SELECT u.UserID, u.UserName, Sum(d.Quantity * i.Price) AS 'Sales'
FROM OrderDetails d
INNER JOIN Orders o ON o.OrderID = d.OrderID
INNER JOIN Item i ON i.ItemID = d.ItemID
INNER JOIN [User] u ON u.UserID = o.UserID
GROUP BY u.UserID, u.UserName

RESULT:
UserID UserName Sales
1 Mobeen 11060
2 Cashier 25960

FOR B:

    SELECT u.UserID, u.UserName, Sum(r.DiscountAmount) AS Discounts
FROM Receipt r
INNER JOIN Orders o ON o.OrderID = r.OrderID
INNER JOIN [User] u ON u.UserID = o.UserID
GROUP BY u.UserID, u.UserName

RESULT:
UserID UserName Discounts
1 Mobeen 50
2 Cashier 310

BUT Then I decided to combine both queries but now the problem is that I am getting different results. The query is as below:

    SELECT u.UserID,
    u.UserName,
    Sum(d.Quantity * i.Price) AS 'Sales',
    Sum(r.DiscountAmount) AS 'Discounts'
FROM OrderDetails d
INNER JOIN Orders o ON o.OrderID = d.OrderID
INNER JOIN Item i ON i.ItemID = d.ItemID
INNER JOIN [User] u ON u.UserID = o.UserID
INNER JOIN Receipt r ON r.OrderID = o.OrderID
GROUP BY u.UserID, u.UserName

Result:
UserID UserName Sales Discounts
1 Mobeen 2270 50
2 Cashier 25760 430

Gordon Linoff · Accepted Answer · 2013-02-06 14:44:03Z

4

The problem is that you are summing along multiple dimensions. If you want to combine them, use subqueries:

select s.userID, s.userName, s.Sales, d.Discounts
from (SELECT u.UserID, u.UserName, Sum(d.Quantity * i.Price) AS 'Sales'
      FROM OrderDetails d
           INNER JOIN Orders o ON o.OrderID = d.OrderID
           INNER JOIN Item i ON i.ItemID = d.ItemID
           INNER JOIN [User] u ON u.UserID = o.UserID
           GROUP BY u.UserID, u.UserName
     ) s join
     (SELECT u.UserID, u.UserName, Sum(r.DiscountAmount) AS Discounts
      FROM Receipt r
           INNER JOIN Orders o ON o.OrderID = r.OrderID
           INNER JOIN [User] u ON u.UserID = o.UserID
       GROUP BY u.UserID, u.UserName
     ) d
     on s.userId = d.userId and s.userName = d.userName

I think what is happening is that you are multiplying the receipt records for each item. You need to be very careful when bringing together data naively using joins. For such aggregations, subqueries are your friend.

If you need to get all the rows (i.e., there may be mismatches on either side), switch the join to a full outer join and use:

select coalesce(s.userId, d.userId) as userId, coalesce(s.userName, d.userName) as userName, . . .

edited Feb 6, 2013 at 14:44

answered Feb 5, 2013 at 15:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

twoleggedhorse Over a year ago

+1 lol, I was just about to post an almost identical answer :)

Muhammad Mobeen Qureshi Over a year ago

@Gordon There is a slight mistake/typo in the post that d.Discount in main select should be d.Discounts

Neville Kuyt · Accepted Answer · 2013-02-05 15:28:51Z

0

The inner join to receipts is probably excluding some orders (not all orders have receipts, right) and double counting others (one order may have many receipts?)

answered Feb 5, 2013 at 15:28

Neville Kuyt

29.6k2 gold badges40 silver badges52 bronze badges

1 Comment

Muhammad Mobeen Qureshi Over a year ago

Yes in my current database, not all orders have receipt but in the real scenario (after deployment) all orders will be having one receipt. And to second point, no, one order may have only one receipt.

Collectives™ on Stack Overflow

Aggregate function SUM returning different values

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related