CODEWITHSUNDEEP
mysqlsql
user6506094
user6506094

Reputation: 1

Select customer type based on top selling product

How can i write a query that looks at customers thats has multiple products and inserts a customer "type: based on the top selling product. For example:

Customer Product Revenue 
123          A       2000     
123          B       500      
123          C       1000

I want to write a query that looks at customer '123' for example and calculates the top selling product and inserts a tag into customer type. In this case 'Customer Type' should be "A" because "A" its the top selling product.

Upvotes: 0

Views: 118

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Hmmm. I assume that "top selling" is referring to the revenue column. If so, this is an aggregation, a join, and a case:

select t.*,
       (case when t.revenue = tt.revenue then 'A' end) as type
from t join
     (select customer, max(revenue) as maxr
      from t
      group by customer
     ) tt
     on t.customer = tt.customer;

Upvotes: 1

Related Questions