Best selling product in a given year

[machumpion]

I have an orders table containing fields: customer, product, orderyear, revenue, quantity

How can I query the best selling product for each customer this year?

I tried setting max(product), but it returned incorrect products.

 

[plog]

It's going to take a subquery. You would build an aggregate query (click the Sigma/Summation symbol in the toolbar). You would have only 2 fields in this query; you would GROUP BY customer and get the MAX of quantity. Save that query as 'sub_BestSellers'.

Then you build another query on your orders table and sub_BestSellers. You would link them via the customer fields and then orders.quantity to the MAX of quantity field in sub_BestSellers. Show all the fields from orders and you have your data. In case of ties, it would show all that had the highest quantity.

 

[machumpion]

what if, I wanted to return the best selling product for each customer only for products where in a corresponding products table, a specific field (i.e. color) is Not Null?

i tried adding the products table in the subquery and adding the Color field, setting criteria to Is Not Null, but in the subsequent query, it returns multiple quantities for different products rather than the best selling one.

 

[plog]

what if, I wanted to return the best selling product for each customer only for products where in a corresponding products table, a specific field (i.e. color) is Not Null?

You would apply that criteria in the subquery with a 'Where' underneath it. Sounds like you left it as 'Group By'.

 

[machumpion]

I only have one addition inquiry about this query. Using MAX in this query will return what product was ordered the most in 1 single order right? If I wanted to determine the most popular product sold throughout the year, would I use SUM in the subquery?

 

[plog]

I don't know what your table holds. Post some sample data from the relevant table(s) to demonstrate what you have and then ask questions about that example data.