how to get the top 3 product sale in a month by country

Question

below is the database from where we need to get the top 3 product sale in a country by month:

USA Lenevo  1/1/2017
USA Toshiba 1/1/2017
USA Dell    1/2/2017
USA Asus    1/2/2017
USA Lenevo  1/1/2017
USA Toshiba 1/1/2017
USA Dell    1/2/2017
USA Lenevo  1/1/2017
USA Toshiba 1/1/2017
USA Dell    1/2/2017

Bangladesh  Lenevo  1/1/2017
Bangladesh  Toshiba 1/1/2017
Bangladesh  Dell    1/2/2017
Bangladesh  Lenevo  1/1/2017
Bangladesh  Toshiba 1/1/2017
Bangladesh  Dell    1/2/2017
Bangladesh  Asus    1/2/2017

India   Lenevo  1/1/2017
India   Toshiba 1/1/2017
India   Dell    1/2/2017
India   Lenevo  1/1/2017
India   Toshiba 1/1/2017
India   Dell    1/2/2017        
India   Asus    1/2/2017

Thailand    Lenevo  1/1/2017
Thailand    Toshiba 1/1/2017
Thailand    Dell    1/2/2017
India           Asus    1/2/2017
Thailand    Lenevo  1/1/2017
Thailand    Toshiba 1/1/2017
Thailand    Dell    1/2/2017
Thailand    Lenevo  1/1/2017
Thailand    Toshiba 1/1/2017

USA Lenevo  2/1/2017
USA Toshiba 2/1/2017
USA Dell    2/2/2017
USA Lenevo  2/5/2017
USA Toshiba 2/6/2017
USA Dell    2/9/2017
USA Lenevo  2/5/2017

Bangladesh  Lenevo  2/1/2017
Bangladesh  Toshiba 2/1/2017
Bangladesh  Dell    2/2/2017
Bangladesh  Lenevo  2/1/2017
Bangladesh  Toshiba 2/1/2017
Bangladesh  Dell    2/2/2017
Bangladesh  Lenevo  2/3/2017

India   Lenevo  2/1/2017
India   Toshiba 2/1/2017
India   Dell    2/2/2017
India   Asus    2/2/2017


Thailand    Lenevo  2/1/2017
Thailand    Toshiba 2/1/2017
Thailand    Dell    2/2/2017
Thailand    Asus    2/2/2017
Thailand    Lenevo  2/1/2017
Thailand    Toshiba 2/1/2017
Thailand    Dell    2/2/2017
Thailand    Lenevo  2/1/2017
Thailand    Toshiba 2/1/2017

(1) It is OK to post homework questions, but you **must** show what you tried, and where you had difficulties. (2) Regarding dates, when you write something like `1/2/2017` without explanation, you are asking us to guess if that is January 2 or if it is 1 February. We can't - we are not mind readers. (3) When you talk about "top 3", ties are possible (first place: six sales, second place: five sales, third place: TWO different products with four sales EACH). In such cases, you must specify what the desired result is. For example: if there are ties, show ALL products tied for third. — , Jan 24 '18 at 21:19

Barbaros Özhan · Answer 1 · 2018-01-24T23:44:00.387

It's quite easy to list top-N queries with fetch first clause, provided you use DB version 12c. Group columns in the order of month, country and brand lastly, and apply grouping function.

The important trick is here to consider the case of multiple existence probability of least valued ordered columns. i.e. you restricted to 3, but count of brands are equal for 4th of 5th column. In such cases, consider to use with ties clause to include all least equal occurrences :

select to_char(sale_date,'yyyymm') "monthOfYear", country, brand, count(1) cnt
  from sales
 group by to_char(sale_date,'yyyymm'), country, brand
 order by count(1) desc
 fetch first 3 rows with ties;

assuming you have a sales table with this columns :

SQL> desc sales
     COUNTRY   VARCHAR2(35)
     BRAND     VARCHAR2(35)
     SALE_DATE DATE

how to get the top 3 product sale in a month by country

1 Answers1