calculate qty and total price - 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)

calculate qty and total price

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-08-21 : 04:15:08

Hi

This is a part of q query I have the need to display the total sum of the "Qty" and dbo.tbl_Order.ProductPrice

SELECT DISTINCT dbo.tbl_Order.NodeID, SUM(dbo.tbl_Order.Quantity) AS Qty

How do I get this to work?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-21 : 04:23:29

Don't quite understand what you want here. Maybe you can post the table DDL, some sample data and the result that you want.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 04:33:47

Try this

SELECT dbo.tbl_Order.NodeID, SUM(dbo.tbl_Order.Quantity) AS Qty, MAX(dbo.tbl_Order.ProductPrice) GROUP BY dbo.tbl_Order.NodeID

Peter Larsson
Helsingborg, Sweden

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-08-21 : 04:44:12

Hi

There are 4 different tables and the complete query looks like this...

SELECT DISTINCT dbo.tbl_Order.NodeID, SUM(dbo.tbl_Order.Quantity) AS Qty, dbo.tbl_Products.Text, dbo.tbl_Login.CustID
FROM dbo.tbl_Order INNER JOIN
dbo.tbl_Products ON dbo.tbl_Order.NodeID = dbo.tbl_Products.NodeId INNER JOIN
dbo.tbl_Login ON dbo.tbl_Order.UserUniqueID = dbo.tbl_Login.UID INNER JOIN
dbo.tbl_OrderInfo ON dbo.tbl_Order.CartID = dbo.tbl_OrderInfo.CartID
WHERE (dbo.tbl_Order.DateOrdered BETWEEN CONVERT(DATETIME, '2006-04-01', 102) AND CONVERT(DATETIME, '2006-05-01', 102))
GROUP BY dbo.tbl_Order.NodeID, dbo.tbl_Products.Text, dbo.tbl_Login.CustID
HAVING (dbo.tbl_Login.CustID = 20)

That query give me a result like this...

NodeID Qty Text CustID
1 200 prod 1 5
2 150 prod 2 5
3 400 prod 3 5

But I would also like to display the total sum like this...

NodeID Qty Text CustID Price TotalSum
1 200 prod 1 5 10 2000
2 150 prod 2 5 5 750
3 400 prod 3 5 87 34800

Hope this help

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 04:51:55

[code]SELECT dbo.tbl_Order.NodeID,
SUM(dbo.tbl_Order.Quantity) AS Qty,
dbo.tbl_Products.Text,
dbo.tbl_Login.CustID,
MAX(dbo.tbl_Products.Price) Price,
SUM(dbo.tbl_Order.Quantity) * MAX(dbo.tbl_Products.Price) TotalSum
FROM dbo.tbl_Order
INNER JOIN dbo.tbl_Products ON dbo.tbl_Order.NodeID = dbo.tbl_Products.NodeId
INNER JOIN dbo.tbl_Login ON dbo.tbl_Order.UserUniqueID = dbo.tbl_Login.UID AND dbo.tbl_Login.CustID = 20
INNER JOIN dbo.tbl_OrderInfo ON dbo.tbl_Order.CartID = dbo.tbl_OrderInfo.CartID
WHERE dbo.tbl_Order.DateOrdered BETWEEN CONVERT(DATETIME, '2006-04-01', 102) AND CONVERT(DATETIME, '2006-05-01', 102)
GROUP BY dbo.tbl_Order.NodeID,
dbo.tbl_Products.Text,
dbo.tbl_Login.CustID[/code]

Peter Larsson
Helsingborg, Sweden

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-08-21 : 05:01:11

Excellent Peter, Thanks :-)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 07:27:33

Thanks for the feed-back!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources