correct qty but totalsum wrong

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

Transact-SQL (2000)

correct qty but totalsum wrong

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-10-27 : 05:33:53

Hi

I have a problem in my query below. The query should retrieve all seperate articles and the total quantity ordered and the totalsum ordered. The problem is that there can be different price for the rows. Like this...

NodeID Qty ProductPrice
659 1 1,05
659 2 2,05

This is my query...

<code>
SELECT DISTINCT
dbo.tbl_Order.NodeID, SUM(dbo.tbl_Order.Quantity) AS Qty, dbo.tbl_Products.Text, SUM(dbo.tbl_Order.Quantity) * MAX(dbo.tbl_Order.ProductPrice)
AS TotalSum
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-10-01', 102) AND CONVERT(DATETIME, '2006-10-27', 102))
GROUP BY dbo.tbl_Order.NodeID, dbo.tbl_Login.CustID, dbo.tbl_Products.Text
HAVING (dbo.tbl_Login.CustID = 20)
</code>

When I run this query it looks fine for almost every row except one. NodeID 659 looks like this..

NodeID Qty Text TotalSum
659 2442 letter 1025640,00

But if I run another query that looks like this..
<code>
SELECT DISTINCT dbo.tbl_Order.NodeID, SUM(dbo.tbl_Order.Quantity) AS Qty, SUM(dbo.tbl_Order.Quantity) * MAX(dbo.tbl_Order.ProductPrice) AS TotalSum
FROM dbo.tbl_Order 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-10-01', 102) AND CONVERT(DATETIME, '2006-10-27', 102))
GROUP BY dbo.tbl_Order.ProductPrice, dbo.tbl_Order.NodeID, dbo.tbl_Login.CustID, dbo.tbl_Products.Text
HAVING (dbo.tbl_Order.NodeID = 659)
</code>

I get this result..

<code>
NodeID Qty TotalSum
659 1 420,00
659 800 1000,00
659 1641 1723,05
</code>

The Qty and Totalsum is here correct, but in my original question only the qty is correct.

Could someone please check this and mayby see whats wrong here?

Regards

M

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 05:37:01

You are using different GROUP BY's...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 05:45:30

Try this

SELECT		dbo.tbl_Order.NodeID,
		dbo.tbl_Order.UserUniqueID CustID,
		SUM(dbo.tbl_Order.Quantity) AS Qty,
		SUM(dbo.tbl_Order.Quantity * dbo.tbl_Order.ProductPrice) AS TotalSum
FROM		dbo.tbl_Order
INNER JOIN	dbo.tbl_Products ON dbo.tbl_Order.NodeID = dbo.tbl_Products.NodeId
WHERE		dbo.tbl_Order.DateOrdered BETWEEN '20061001' AND '20061027'
--		AND dbo.tbl_Order.UserUniqueID = 20
		AND dbo.tbl_Order.NodeID = 659
GROUP BY	dbo.tbl_Order.NodeID,
		dbo.tbl_Login.CustID
ORDER BY	dbo.tbl_Order.NodeID,
		dbo.tbl_Login.CustID

Peter Larsson
Helsingborg, Sweden

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-10-27 : 07:13:44

Hi Peter

Thanks for your reply, one thing though. I need to modify the first query in my post that also use the tbl_OrderInfo and the tbl_Login table. Your suggestion missed those tables, maybe you changed the query based on the second query. Is it possible to change the first query so it give me the correct totalsum?

Best regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 07:17:20

Why would you need to JOIN the tbl_OrderInfo? Nothing in the SELECT, GROUP BY or WHERE uses that table.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 07:20:10

It depends on the data in the tables. For every record in a JOINed table that has multiple values, the JOIN returns too many records.
Give us same sample data for each of the involved tables.

Peter Larsson
Helsingborg, Sweden

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-10-27 : 07:41:48

Hi Peter

You are right I don't need the orderinfo table, it was there left there by mistake. I copied the query from another stored procedure where that table was neccesary to select based on information in that table.

The tbl_Products contains information about the product, text and price for example, the tbl_Order also contains the price column but the difference between those two tables is that the tbl_products table contains the actual price of the product today. The tbl_Order contains the price of the product when it was ordered. So if a client ordered a product 2 months ago that product cost 20$ but 1 month later the product cost 25$. So my query should show a totalsum based on the price value that is in the tbl_Order table. I use this query to show statistic, therefore I need to show a totalsum of what the products cost when the order was made, not what the cost would be today based on what the product would cost today.

Hope this all make sence. Do you need any more information, please let me know.

Best regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 07:56:56

Give us same sample data for each of the involved tables.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 08:18:55

Did you even try my last query?

SELECT		dbo.tbl_Order.NodeID,
		dbo.tbl_Products.Text,
		SUM(dbo.tbl_Order.Quantity) AS Qty,
		SUM(dbo.tbl_Order.Quantity * dbo.tbl_Order.ProductPrice) AS TotalSum
FROM		dbo.tbl_Order
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 '20061001' AND '20061027'
		AND dbo.tbl_Login.CustID = 20
GROUP BY	dbo.tbl_Order.NodeID,
		dbo.tbl_Products.Text

Peter Larsson
Helsingborg, Sweden

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-10-27 : 08:22:38

Hi again

Hope this will do....

tbl_Order

UserUniqueID NodeID Qty ProductPrice DateOrdred

116 659 1 1.05 2006-10-20 00:00:00
112 659 400 1.05 2006-10-12 00:00:00
160 659 400 1.25 2006-08-21 00:00:00
54 659 100 1.25 2006-06-09 00:00:00
54 659 400 1.25 2006-06-13 00:00:00

tbl_Products

NodeID Text

380 Book 1
386 Book 2
387 Book 3
416 Book 4
659 Book 5

tbl_Login

UID CustID

40 20
41 20
42 20
116 20
44 20

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 08:41:25

[code]SELECT p.NodeID,
p.[Text],
SUM(o.Qty) Qty,
SUM(o.Qty * o.ProductPrice) Price
FROM dbo.tbl_Order o
INNER JOIN dbo.tbl_Products p ON p.NodeID = o.NodeID
INNER JOIN dbo.tbl_Login l ON l.UID = o.UserUniqueID
WHERE o.DateOrdered BETWEEN '20061001' AND '20061027'
AND l.CustID = 20
GROUP BY p.NodeID,
p.[Text][/code]
Peter Larsson
Helsingborg, Sweden

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-10-27 : 09:27:55

Hi Peter

That worked very well, Thank you very much! Really appreciate it.

Best Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-27 : 09:36:54

Good. Third time's a charm...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources