total sum is not correct... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

total sum is not correct...

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-08-22 : 08:56:42

Hi

I have a little problem with a query that is supposed to display the total sum of ordered products price based on month. Like this...

Qty TotalSum MonthName MonthNumber
21250 80360 April 4

I use this query to get the result above.

SELECT DISTINCT
SUM(dbo.tbl_Order.Quantity) AS Qty, SUM(dbo.tbl_Order.Quantity) * MAX(dbo.tbl_Products.ProductPrice) AS TotalSum, DATENAME(mm,
dbo.tbl_Order.DateOrdered) AS MonthName, MONTH(dbo.tbl_Order.DateOrdered) AS MonthNumber
FROM dbo.tbl_Order INNER JOIN
dbo.tbl_OrderInfo ON dbo.tbl_Order.CartID = dbo.tbl_OrderInfo.CartID INNER JOIN
dbo.tbl_Login ON dbo.tbl_Order.UserUniqueID = dbo.tbl_Login.UID INNER JOIN
dbo.tbl_Products ON dbo.tbl_Order.NodeID = dbo.tbl_Products.NodeId
WHERE (dbo.tbl_Order.DateOrdered BETWEEN CONVERT(DATETIME, '2006-04-01', 102) AND CONVERT(DATETIME, '2006-04-30', 102))
GROUP BY dbo.tbl_Login.CustID, DATENAME(mm, dbo.tbl_Order.DateOrdered), MONTH(dbo.tbl_Order.DateOrdered)
HAVING (dbo.tbl_Login.CustID = 20)

But the weird thing is that the total qty is correct but the totalsum is not correct..!!?

I can see this by running this query...

SELECT DISTINCT
SUM(dbo.tbl_Order.Quantity) AS Qty, SUM(dbo.tbl_Order.Quantity) * MAX(dbo.tbl_Products.ProductPrice) AS TotalSum, DATENAME(mm,
dbo.tbl_Order.DateOrdered) AS MonthName, MONTH(dbo.tbl_Order.DateOrdered) AS MonthNumber, dbo.tbl_Products.NodeId,
dbo.tbl_Products.Text
FROM dbo.tbl_Order INNER JOIN
dbo.tbl_OrderInfo ON dbo.tbl_Order.CartID = dbo.tbl_OrderInfo.CartID INNER JOIN
dbo.tbl_Login ON dbo.tbl_Order.UserUniqueID = dbo.tbl_Login.UID INNER JOIN
dbo.tbl_Products ON dbo.tbl_Order.NodeID = dbo.tbl_Products.NodeId
WHERE (dbo.tbl_Order.DateOrdered BETWEEN CONVERT(DATETIME, '2006-04-01', 102) AND CONVERT(DATETIME, '2006-04-30', 102))
GROUP BY dbo.tbl_Login.CustID, DATENAME(mm, dbo.tbl_Order.DateOrdered), MONTH(dbo.tbl_Order.DateOrdered), dbo.tbl_Products.NodeId,
dbo.tbl_Products.Text
HAVING (dbo.tbl_Login.CustID = 20)

When I run that query I see all the different products that have been ordered and if I add the seperate qty values together I get the same amount as in the query above, but if I do the same with the totalsum value, I get a different value.

I can't see what I do wrong that might cause this problem. Can someone please help me out?

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 09:09:10

The reasons are the joins. Most probably you get dupes. Try this

SELECT		SUM(dbo.tbl_Order.Quantity) / COUNT(dbo.tbl_Login.CustID) AS Qty,
		SUM(dbo.tbl_Order.Quantity) * MAX(dbo.tbl_Products.ProductPrice) / COUNT(dbo.tbl_Login.CustID) AS TotalSum,
		DATENAME(mm, dbo.tbl_Order.DateOrdered) AS MonthName,
		MONTH(dbo.tbl_Order.DateOrdered) AS MonthNumber
FROM		dbo.tbl_Order
INNER JOIN	dbo.tbl_OrderInfo ON dbo.tbl_Order.CartID = dbo.tbl_OrderInfo.CartID
INNER JOIN	dbo.tbl_Login ON dbo.tbl_Order.UserUniqueID = dbo.tbl_Login.UID
INNER JOIN	dbo.tbl_Products ON dbo.tbl_Order.NodeID = dbo.tbl_Products.NodeId
WHERE		dbo.tbl_Order.DateOrdered BETWEEN CONVERT(DATETIME, '2006-04-01', 102) AND CONVERT(DATETIME, '2006-04-30', 102)
		AND dbo.tbl_Login.CustID = 20
GROUP BY	dbo.tbl_Login.CustID,
		DATENAME(mm, dbo.tbl_Order.DateOrdered),
		MONTH(dbo.tbl_Order.DateOrdered)

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 09:11:29

Ot simpler, try to change
MAX(dbo.tbl_Products.ProductPrice)
to
AVG(dbo.tbl_Products.ProductPrice)

Peter Larsson
Helsingborg, Sweden

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-08-22 : 09:26:31

Hi Peter

That gave me wrong qty and wrong totalsum in both of your suggestions.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 09:38:07

What is wrong with the TotalSum?
Do you have some sample data or sample output to show? Also what the correct output would be?

Peter Larsson
Helsingborg, Sweden

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-08-22 : 10:05:02

Hi

The output I get is this...

Qty TotalSum MonthName MonthNumber
959 3165,69767441860 April 4

But it should be like this...

Qty TotalSum MonthName MonthNumber
41250 80376 April 4

If I change from ...
SUM(dbo.tbl_Order.Quantity) / COUNT(dbo.tbl_Login.CustID) AS Qty
to..
SUM(dbo.tbl_Order.Quantity) AS Qty

Then I get the correct Qty. But the totalsum should be 80376.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 12:00:45

Try this one

SELECT		SUM(dbo.tbl_Order.Quantity) AS Qty,
		SUM(dbo.tbl_Order.Quantity * dbo.tbl_Products.ProductPrice) AS TotalSum,
		DATENAME(mm, dbo.tbl_Order.DateOrdered) AS MonthName,
		MONTH(dbo.tbl_Order.DateOrdered) AS MonthNumber
FROM		dbo.tbl_Order
INNER JOIN	dbo.tbl_OrderInfo ON dbo.tbl_Order.CartID = dbo.tbl_OrderInfo.CartID
INNER JOIN	dbo.tbl_Login ON dbo.tbl_Order.UserUniqueID = dbo.tbl_Login.UID
INNER JOIN	dbo.tbl_Products ON dbo.tbl_Order.NodeID = dbo.tbl_Products.NodeId
WHERE		dbo.tbl_Order.DateOrdered BETWEEN CONVERT(DATETIME, '2006-04-01', 102) AND CONVERT(DATETIME, '2006-04-30', 102)
		AND dbo.tbl_Login.CustID = 20
GROUP BY	dbo.tbl_Login.CustID,
		DATENAME(mm, dbo.tbl_Order.DateOrdered),
		MONTH(dbo.tbl_Order.DateOrdered)

Peter Larsson
Helsingborg, Sweden

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-08-22 : 12:28:33

Hi Peter

Worked like a charm ;-)

Thanks a lot for your help.

Best Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources