I have a store database with links like this:
My task is that I need to display the number of products (unique – those that do not repeat). That is, conditionally, I need to count all the goods purchased by the user, but if I have already counted some goods, do not count it (because it has already been counted).
How can the problem be solved?
>Solution :
SELECT
orders.buyer_id,
users.first_name || ' ' || users.last_name,
product.name,
Sum(products_to_orders.quantity)
FROM orders
left JOIN products_to_orders ON product_to_orders.order_id = orders.id
left JOIN products ON products.id = products_to_orders.product_id
left JOIN users ON users.id = orders.buyer_id
GROUP BY orders.buyer_id, users.first_name || ' ' || users.last_name, product.name
Group by might be scuffed but I can’t really test this