Sum column multiple times

2
votes

I have a table with some products. Each of them have a price. When a user sees his basket, I want to display the total amount of all the products he selected.

My question is : is it possible to do it via SQL or have I to do it after the request in PHP ?

Actually my request is :

SELECT SUM(PRICE) FROM products WHERE ID IN(65,68,68);

And I would the two 68 to be taken in consideration.

Thank you, Florian

3
mysqlsqlsum
you're passing the id's in the in by parameter ? – Gonzalo.-
Are you saving the user's basket in your database? Or is it just in your session object or a cookie? – lc.

3 Answers

5
votes

So ideally you would not use IN in this case (in fact, you can't get it to work that way). What you need is a JOIN to the cart_products table (or similar): 

SELECT SUM(PRICE) 
FROM 
  cart_products
  JOIN products ON products.id = cart_products.product_id
WHERE 
  cart_products.cart_id = ?

And then pass in the current session's cart_id.

Note that if your cart has a qty option, you can use this as the SUM expression:

SUM(products.PRICE * cart_products.qty)
0
votes

You could dynamically generate your SQL so you end up with something like:

SELECT SUM(PRICE)
FROM
(
    SELECT PRICE FROM products WHERE id = 65
    UNION ALL
    SELECT PRICE FROM products WHERE id = 68
    UNION ALL
    SELECT PRICE FROM products WHERE id = 68
) x

but I wonder if there's really a point. Presumably you already have the unit price displayed on the page, so can't you just add it up from what you already have?

0
votes

I think a way, is to insert in a temporally table all your items on basket, and then join it and get the products. So, if you have three times a product, you will got it inserted three times, and the join will be three times, so when you make the sum, will get the correct import.

This is if you're passing every Id as parameter from PHP

I'm not sure about MySQL syntax, but this should work

CREATE TEMPORARY TABLE BasketProducts(Id INT UNSIGNED NOT NULL)

Insert into BasketProducts values (--Insert everyId)

Then do the join

Select sum(P.price) from Products P
inner join BasketProducts  BP on P.Id = BP.Id