Find Top 5 Customers for Beverages based on their total purchase value SQL

Question

Here is the link to the Data Set. https://www.w3schools.com/sql/trysql.asp?filename=trysql_asc

I have been trying to solve this but couldn't find a way to get the total purchase value while grouping with the customer table

Answer 1

Something like that?

SELECT c.customerid,
       Sum(p.price)
FROM   customers AS c
       INNER JOIN orders AS o
               ON o.customerid = c.customerid
       INNER JOIN orderdetails AS od
               ON od.orderid = o.orderid
       INNER JOIN products AS p
               ON p.productid = od.productid
GROUP  BY c.customerid
ORDER  BY Sum(p.price) DESC
LIMIT  5 

Just following on from your quantity comment...

SELECT c.customerid,
       Sum(p.price),
       Sum(p.price * od.quantity)
FROM   customers AS c
       INNER JOIN orders AS o
               ON o.customerid = c.customerid
       INNER JOIN orderdetails AS od
               ON od.orderid = o.orderid
       INNER JOIN products AS p
               ON p.productid = od.productid
GROUP  BY c.customerid
ORDER  BY Sum(p.price) DESC
LIMIT  5