How to filter top customer with top product ordered?

36 Views Asked by At

I want to filter top 2 customers with their top 2 products they had ordered over a period.

Date Customer Product Qty Sales
1/10/2023 Mike Apple 1 3.00
1/10/2023 Joyce Apple 1 3.00
2/10/2023 Naomi Orange 1 2.00
2/10/2023 Naomi Apple 1 3.00
3/10/2023 Naomi Orange 1 2.00
3/10/2023 Mike Orange 1 2.00
4/10/2023 Naomi Banana 1 1.00
4/10/2023 Mike Orange 1 2.00
4/10/2023 Mike Grapes 1 1.00
5/10/2023 Mike Orange 1 2.00
5/10/2023 Helen Orange 1 2.00

See codes that l have tried

 `SELECT 
 TOP 5 O.Customer, O.CustomerId
 FROM Orders O
 JOIN (
 SELECT Customer, TOP 2 Product, Sum(Sales) as Sales
 FROM OrderDetail O2
 GROUP BY Customer, Product
 ORDER BY SUM(OrderDetail.Sales) DESC
 ) O2 ON O2.CustomerId = O.CustomerId
 ORDER BY O.CustomerId
 `

Expected output

Customer Total Sales Orange Apple
Naomi 9.00 6.00 3.00
Mike 7.00 4.00 3.00
0

There are 0 best solutions below