CODEWITHSUNDEEP
mysqlselect
axelrod miles
axelrod miles

Reputation: 3

SELECT top 3 customer with highest order count from Customer table and get sum of total for each customer from orders table

Customer table Customer table

Orders Table Orders table

I want to select top 3 customers with highest order count and the SUM of total from Orders for each customer id (custid)

I tried this

SELECT c.custid, c.name, c.order_count, SUM( o.total ) AS Totalspent 
FROM customer c INNER JOIN orders o ON c.custid = o.custid GROUP BY c.order_count DESC LIMIT 3

it fetches the data and groups by the order_count but the total is wrong. How do I get the correct total from orders for each customer (custid) while selecting the top 3 customers based on order count?

Upvotes: 0

Views: 107

Answers (1)

Elijah Brandyberry
Elijah Brandyberry

Reputation: 207

You will want to group by the custid (and any other columns you want to select without aggregrating) as well. Then order the results by order_count to ensure the 3 selected have the highest order count.

SELECT c.custid, c.name, c.order_count, SUM( o.total ) AS Totalspent 
FROM customer c 
INNER JOIN orders o ON c.custid = o.custid 
GROUP BY c.custid, c.name, c.order_count
ORDER BY c.order_count DESC
LIMIT 3

Hopefully this helps!

Upvotes: 0

Related Questions