Forum Discussion

PDRTXRA's avatar
PDRTXRA
Helper I
3 years ago

Best Product per Client

Hello.

I have two tables. One with clients. One with sales.

I want to create 3 columns in my clients table.

 

First column: Name of the product in which they spent the most money.

Second column: Name of the product in second place.

Third column: Name of the product in third place.

 

To clarify: I need the results to show the product in which the client spent the most money overall. So, for example, if a client spent 20€ on boots  and 50€ on 10 t-shirts, it should say t-shirt, even tho boots are more expensive than t-shirts.

 

Besides that, I need to filter the sales table since I don't want all transactions to be considered. Let's say I want to filter by ProductCategory. 

 

ClientIDNameLastName
1JohnJohnson
2MarySmith
3PeterMiller

 

TransactionIDClientIDProductNameSalesValueProductCategory
12Shirt5A
21Sweater15C
33Shirt5A
43Pants10D
52Socks1D
61Boots20E
71Shirt5A
82Boots20E
92Sweater15C

 

Thank you in advance. 

2 Replies