如何在数据库中获取多列的Mysql SUM

Question

I want to calculate total products in cart for one user by summing values for each product in cart.

I'm using this query :

    SELECT *, SUM(quantity) OVER (PARTITION BY product_id ORDER BY id) AS TotalProducts 
    FROM cart WHERE user_id ='$user_id';

Getting error:

SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(PARTITION BY product_id ORDER BY id)

EDIT with DB schema

id | product_id | quantity | user_id
1  | 37         | 2        | 23847
2  | 70         | 2        | 23847

I can't see what I'm doing wrong?

Answer 1

I'm not familiar with the MariaDB syntax, and you didn't show your schema, but here's my shot at an answer:

SELECT c.*,
    SUM(c.quantity) as `TotalProducts`
FROM cart c
WHERE c.user_id = '$user_id'
GROUP BY c.user_id

Or, if you want a total per product, you could use:

SELECT c.*,
    SUM(c.quantity) as `TotalProducts`
FROM cart c
WHERE c.user_id = '$user_id'
GROUP BY c.user_id, c.product_id