trying to calcuate total price of shopping cart

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

General SQL Server Forums

New to SQL Server Programming

trying to calcuate total price of shopping cart

Author

Topic

paulc1976
Starting Member

4 Posts

Posted - 2009-04-22 : 06:29:17

Hi everyone,

Im trying to get my asp.net website to display the total price of an order on the masterpage of my site so the user will know how much they have spent, I've already done this with displaying the number of items in the shopping cart and that works fine.
But that was with 1 table so was pretty straightforward.

My knowledge of SQL is very poor so I've been using the query builder trying to get this thing to work

Basically the cart table (Comp_Cart) holds the quantity field and the products table (Comp.Products) holds the price for each product, the product ID from cart table = product ID from products table.
Also each cart has its own session ID (bit like a user ID) so im putting this in as a parameter so it returns the price for that user.
I figured that the calculation would be something like SUM(qty*price) AS TOTAL

The query builder gives this (with some mods by myself):

SELECT Comp_Cart.SessionID, Comp_Cart.Product_ID, Comp_Products.Product_ID AS Expr1, SUM(Comp_Products.NetPrice * Comp_Cart.qty) AS TOTAL, Comp_Cart.SessionID AS Expr2

FROM Comp_Cart INNER JOIN
Comp_Products ON Comp_Cart.Product_ID = Comp_Products.Product_ID

WHERE (Comp_Cart.SessionID = @SessionID)

GROUP BY Comp_Cart.SessionID

But when I try to run it an SQL exception is thrown Column Comp_Cart.ProductID' is invalid in the select list because it is not contained in either an aggregate function or the group by clause??

I have no idea what this means! Can anyone help me modify this query so it will work?

thanks,

Paul

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-22 : 06:36:04

All you need is
(I've added the number of items)

SELECT SUM(Comp_Products.NetPrice * Comp_Cart.qty) AS TOTAL ,
totalitems = sum(Comp_Cart.qty)
FROM Comp_Cart
INNER JOIN Comp_Products
ON Comp_Cart.Product_ID = Comp_Products.Product_ID
WHERE Comp_Cart.SessionID = @SessionID

But if you want to return the session id as well

SELECT Comp_Cart.SessionID,
SUM(Comp_Products.NetPrice * Comp_Cart.qty) AS TOTAL
FROM Comp_Cart
INNER JOIN Comp_Products
ON Comp_Cart.Product_ID = Comp_Products.Product_ID
WHERE (Comp_Cart.SessionID = @SessionID)
GROUP BY Comp_Cart.SessionID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 06:37:04

[code]SELECT c.SessionID,
c.Product_ID,
SUM(p.NetPrice * c.qty) OVER (PARTITION BY Comp_Cart.Product_ID) AS TOTAL,
c.SessionID AS Expr2
FROM Comp_Cart AS c
INNER JOIN Comp_Products AS p ON p.Product_ID = c.Product_ID
WHERE c.SessionID = @SessionID[/code]or[code]SELECT c.SessionID,
c.Product_ID,
SUM(c.qty) AS Qty,
SUM(p.NetPrice * c.qty) AS Total
FROM Comp_Cart AS c
INNER JOIN Comp_Products AS p ON p.Product_ID = c.Product_ID
WHERE c.SessionID = @SessionID
GROUP BY c.SessionID,
c.Product_ID[/code]

E 12°55'05.63"
N 56°04'39.26"

paulc1976
Starting Member

4 Posts

Posted - 2009-04-22 : 07:30:27

thanks guys, got it working thanks to your help, saved me many hours of frustration!

thanks again

Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources