How to check which product is most popular (1 Viewer)

[hansamuq]

Hi Gurus,

I have a table like this.

ProductCode, Qty, SalesPrice, Customer
1001 , 10 , $1000 , A
1001 , 10 , $1000 , B
2001 , 10 , $3000 , A
3001 , 15 , $3000 , A
4001 , 15 , $10000 , B
1001 , 20 , $2000 , B

I will like to query, which customer give me the most sales. And for that customer, which product is most popular.
From the simple table above, Customer B give me the most sales, and Product 4001 is the most popular product for customer B.


I am able to get the most sales by creating a total sum query based on SalesPrice. But how do i find out which product is more popular for that customer?

 

[Galaxiom]

Group By ProductCode and Customer

 

[hansamuq]

Group By ProductCode and Customer

Thanks Galaxiom!

when you mean group by productcode and customer, i can see which customer buy the highest qty of the product but how to get the most popular product for the highest sales customer?

 

[Grumm]

Try something like this :

SELECT Sum(Tabel1.SalesPrice) AS SomVanSalesPrice, Tabel1.ProductCode, Tabel1.Customer, Tabel1.Qty
FROM Tabel1
GROUP BY Tabel1.ProductCode, Tabel1.Customer, Tabel1.Qty
Order by Tabel1.Qty DESC,Sum(Tabel1.SalesPrice) DESC

 

[hansamuq]

Try something like this :

SELECT Sum(Tabel1.SalesPrice) AS SomVanSalesPrice, Tabel1.ProductCode, Tabel1.Customer, Tabel1.Qty
FROM Tabel1
GROUP BY Tabel1.ProductCode, Tabel1.Customer, Tabel1.Qty
Order by Tabel1.Qty DESC,Sum(Tabel1.SalesPrice) DESC

Hi Grumm,

Thanks, it does give me the biggest spender. Customer B.
But it only shows the highest qty; (i had made append a line on to the table)
Customer total bought;
product 1001 = 30qty (1 x 10, 1 x 20)
product 4001 = 15qty
So it should show me the 1001 because customer bought the most qty of that.

How can i do that?

 

[JHB]

The below should give you what you look for:

SELECT Top 1 Sum(Qty) AS SumOfQty, ProductCode, Customer
FROM YourTable
GROUP BY ProductCode, Customer
ORDER BY Sum(Qty) DESC;

 

[Grumm]

My query should add all the qty for one product and put the highest one on top.

But i agree with JHB. Maybe you can use 2 querys because it is possible that the biggest spender is on the first result but you get on the second result the highest qty.

 

[hansamuq]

Thanks JHB and Grumm.
How should i use 2 queries? i can find the biggest spender by using SUM on salesprice.
But how could i use another query to query the most popular product for THAT customer

JHB code gaves me the highest total QTY sum of the product
GRUMM code gaves me the highest QTY of the product
What i need is the highest total qty sum of the product based on the biggest spender.