SQL- top five sold products to different categories

Asked Dec 01 '17 at 03:32

Active Dec 01 '17 at 03:32

Viewed 30 times

I have three sql tables

Orders:       Clients:   Products:
id            id         id
product_id    gender     category_name
client_id     city

and i need to output top 5 product_id's for each category_name grouped by gender and city. For example

category1  category1  category1  category1
male       female     male       female 
city1      city1      city2      city2
top5       top5       top5       top5

category2  category2  category2  category2
male       female     male       female 
city1      city1      city2      city2
top5       top5       top5       top5
........................................
etc.

How do i get that? Do i need to left join clients and products to orders, then group by category, gender and city and finally apply sorting and limit to 5?

asked Dec 01 '17 at 03:32

Superbman

Go and check with the link: https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results – DineshDB Dec 01 '17 at 03:44
@DineshDB your example doesn't involve counting of repeated products. E.g. product_name num_purchases toothbrush 967 – Superbman Dec 01 '17 at 03:52
@Barmar i need to count those things not just filter them – Superbman Dec 01 '17 at 03:56
@Superbman Use one of the techniques in that answer on the result of a query that uses `COUNT(*)` with `GROUP BY`. – Barmar Dec 01 '17 at 03:57
well, that didn't help much :/ – Superbman Dec 01 '17 at 04:04
@Superbman I tried something. Check it out [Trial Demo](http://sqlfiddle.com/#!9/3a445b/7) – cdaiga Dec 01 '17 at 05:13
@cdaiga looks great, that's almost what i want. Is it possible to add how many times those products were purchased? – Superbman Dec 01 '17 at 05:36
@cdaiga After reading some of the suggested questions, I tried and wrote this query but it returned incorrect information :( https://pastebin.com/1NdQZemP – Superbman Dec 01 '17 at 05:42

SQL- top five sold products to different categories

0 Answers0