All geek questions in one place

How to sum the cost of products in the basket per user

I am trying to move some of the logic of my web store application into a database engine, so I decided that shopping cart pricing would be a good start. Therefore, I have the relationship shown below with the Cart_product table with foreign keys with buyer and product. The total basket price for each user will be the price of each product in Cart_product multiplied by this amount. How and with what can I achieve this? Trigger, procedure, cursor? Any help appreciated.

enter image description here

+4
source share
1 answer
SELECT Buyer_ID, SUM(Amount * Product.ProductPrice) FROM Cart_product LEFT JOIN Product on Cart_product.Product_ID = Product.Product_ID GROUP BY Buyer_ID

will return how much each user bought. What you do with it is up to you.

For a specific user:

 SELECT SUM(Amount * Product.ProductPrice) FROM Cart_product LEFT JOIN Product on Cart_product.Product_ID = Product.Product_ID WHERE Buyer_ID = XXX GROUP BY Buyer_ID
+5
source

Source: https://habr.com/ru/post/1337421/

More articles:


All Articles