Cart System Query 1

[newmediaguy]

Hi Guys

I am having trouble achieving the following to things.....

1. Get the total number of Items from the 'cart table'
2. Get the total value of the order

the database has a table containing cart info [id, order id, productid, qty] and the product table has all product information [productid, price, description]

I can total the items, but am having trouble getting the total order value

any help or suggestions would be appreciated.

I can provide both of the table structures if required.

Thanks

Glen

 

[amorous]

YES, provide some sample data and the kind of result you want.

-VJ

 

[newmediaguy]

thanks for the prompt response.

---------------------------------------
orderid | totalproductsonorder | totalordervalue
000001 6 $54.98

above is what i am trying to achieve
---------------------------------------
this is the code so far.........the math is just to get the sale price as markup is stored with each product
---------------------------------------

SELECT     Cart.*, ProductTable.OurSellPrice / 100 * ProductTable.Percentage + ProductTable.OurSellPrice AS Total
FROM         Cart INNER JOIN
                      ProductTable ON Cart.TPID = ProductTable.TPID
WHERE     (Cart.SessionID = '1021353186')

thanks

Glen

 

[amorous]

What is the error you are getting?

It is always better to explicitly mention the field names instead of * when you are using joins.

Try something like this:

SELECT Cart.Field1,Cart.Field2, Count(*) AS TotalproductsOrder,FORMATCURRENCY(((ProductTable.OurSellPrice / 100) * (ProductTable.Percentage) + (ProductTable.OurSellPrice)),2) AS Total
FROM Cart INNER JOIN
ProductTable ON Cart.TPID = ProductTable.TPID
WHERE (Cart.SessionID = '1021353186')

-VJ

 

[newmediaguy]

Thanks for that, i am still struggling with it though.

results
------------------------------------
total | No. Items
------------------------------------
$85.65 2
$69.78 1

required
------------------------------------
total | no. Items
------------------------------------
$155.43 3


What im using is as follows:

SELECT     COUNT(*) AS Expr1, ProductTable.OurSellPrice / 100 * ProductTable.Percentage + ProductTable.OurSellPrice AS Total, 
                      SUM(Cart.Quantity + Cart.Quantity) AS Expr2, Cart.Quantity
FROM         Cart INNER JOIN
                      ProductTable ON Cart.TPID = ProductTable.TPID
WHERE     (Cart.SessionID = '1021353179')
GROUP BY ProductTable.OurSellPrice / 100 * ProductTable.Percentage + ProductTable.OurSellPrice, Cart.Quantity

 

[SQLBill]

To get this result:

1. Get the total number of Items from the 'cart table'
2. Get the total value of the order

Try this:

SELECT SUM(Cart.Quantity) AS [Total Orders],
       SUM(ProductTable.OurSalePrice) AS [Total Cost]
FROM Cart
JOIN ProductTable
ON Cart.TPID = ProductTable.TPID

-SQLBill