1
00:00:00,960 --> 00:00:03,540
In this video, we will learn about how we can get.

2
00:00:04,880 --> 00:00:07,470
Top subcategories per category.

3
00:00:08,240 --> 00:00:18,080
So here you can see we have product categories and then we have Pader subcategories and here the third

4
00:00:18,080 --> 00:00:28,340
column will contain the total number of products but subcategory and we need to know the top two subcategories

5
00:00:28,340 --> 00:00:29,330
per category.

6
00:00:29,330 --> 00:00:31,760
In the above picture, you can see we have one category.

7
00:00:31,760 --> 00:00:34,510
Bikes and bikes contain three subcategories.

8
00:00:35,450 --> 00:00:42,190
And these are the different number of products that are available in this sub category.

9
00:00:42,740 --> 00:00:51,740
And we need to display top two subcategory for each category for this product bikes, which would display

10
00:00:52,250 --> 00:00:53,530
like road bikes.

11
00:00:53,540 --> 00:00:59,870
This is the top most because this category contains the highest amount of products.

12
00:00:59,870 --> 00:01:02,720
And then the second will be mountain bikes.

13
00:01:02,750 --> 00:01:08,690
And in the same way and the components and all the other categories that are available in the dataset,

14
00:01:09,410 --> 00:01:14,590
in the below image, you can see the required output.

15
00:01:15,080 --> 00:01:19,220
So we need to display the categories.

16
00:01:19,370 --> 00:01:27,770
And then for each category, we need to display top two subcategories for like these are the top tossup

17
00:01:27,770 --> 00:01:30,700
category for the component clothing, accessories.

18
00:01:31,400 --> 00:01:37,610
So for all the available categories, we need to display top two subcategories.

19
00:01:38,060 --> 00:01:45,170
And this should be based on the total number of products available for that subcategory.

20
00:01:45,390 --> 00:01:48,050
Let's go to the power created for this example.

21
00:01:48,060 --> 00:01:51,770
We need to categories of category and productivity.

22
00:01:51,830 --> 00:02:00,770
Let's combine these three tables with each other so they click on the Mouskouri Much Korea's new product

23
00:02:00,770 --> 00:02:01,280
category.

24
00:02:01,460 --> 00:02:03,630
And then we need product subcategories.

25
00:02:05,780 --> 00:02:09,620
This is the primary key and this one is the foreign key.

26
00:02:10,160 --> 00:02:12,320
And we need Energiewende, OK?

27
00:02:16,820 --> 00:02:19,250
And here we can rename

28
00:02:25,010 --> 00:02:35,000
categories subcategories, here you can see one table that contains the information, multiple subcategories.

29
00:02:36,170 --> 00:02:39,200
So we can remove the unnecessary columns.

30
00:02:45,760 --> 00:02:48,070
And I'll try to add product.

31
00:02:49,610 --> 00:02:56,570
Now we need to join with the productivity again, much curious new sorry for the formatting, we need

32
00:02:57,500 --> 00:02:59,960
subcategory keys for this.

33
00:02:59,960 --> 00:03:06,980
We have to take a broader subcategory key and in this part of subcategory name.

34
00:03:09,500 --> 00:03:13,430
Here you can write subcategories.

35
00:03:16,600 --> 00:03:18,460
And not too much as new.

36
00:03:24,860 --> 00:03:27,800
Products and we need a subcategory.

37
00:03:31,200 --> 00:03:32,890
Part of secretary energy.

38
00:03:34,780 --> 00:03:35,170
OK.

39
00:03:37,020 --> 00:03:40,320
And this will be the final day, both our top.

40
00:03:41,890 --> 00:03:42,880
Subcategory.

41
00:03:47,300 --> 00:03:56,630
This is an amateur table, so now we have product category and we don't need this column and we have

42
00:03:56,930 --> 00:04:01,460
the sub category and this is the table that contains the product information.

43
00:04:03,140 --> 00:04:09,150
And from the product table, we are interested only in the product.

44
00:04:10,010 --> 00:04:13,400
So we need to add one column here, add column.

45
00:04:20,690 --> 00:04:25,270
The project project underscore key.

46
00:04:28,240 --> 00:04:37,510
And here we have the right column, epigastric column, so it needs the table, the table is product.

47
00:04:45,600 --> 00:04:49,710
And then we are interested in the product key.

48
00:04:56,360 --> 00:04:56,900
Yes, it.

49
00:04:58,560 --> 00:05:10,950
OK, so here you can see we are getting the product column and now we need to count the number of products

50
00:05:10,950 --> 00:05:18,830
for each subcategory so we can do it with the same step and go to the Lescott, the.

51
00:05:21,160 --> 00:05:23,920
Setting and here we have to read.

52
00:05:26,680 --> 00:05:32,410
One more function, Rockhound Tabletop Rockhound.

53
00:05:39,730 --> 00:05:40,270
That's it.

54
00:05:41,810 --> 00:05:50,780
OK, so here we got the number of products for every subcategory I command and basically a 30 year old

55
00:05:50,780 --> 00:05:56,720
basically at 65 caps, we had three so we can delete this column.

56
00:05:58,170 --> 00:06:01,220
OK, so now we have.

57
00:06:02,230 --> 00:06:04,030
Category subcategory and.

58
00:06:05,980 --> 00:06:12,430
And now we need to be rapping on English category.

59
00:06:13,760 --> 00:06:20,840
So I need to click goodbye and here we need in this category name.

60
00:06:22,070 --> 00:06:32,360
And we need all the rules against each category only to click OK, and here you can see buycks companies,

61
00:06:32,360 --> 00:06:36,320
clothing and accessories and this column count.

62
00:06:37,070 --> 00:06:41,770
This column contains a subcategory and product key.

63
00:06:42,050 --> 00:06:48,000
And if you click the component, so here you can see we are getting.

64
00:06:48,020 --> 00:06:48,820
Let me zoom it.

65
00:06:50,270 --> 00:06:54,450
So here you can see we are getting 14, three one, then 40.

66
00:06:55,400 --> 00:07:05,270
So how we can get the top products first we need to sort this table and once it will be sorted and then

67
00:07:05,270 --> 00:07:09,780
we can get the top two or three or any number that you want.

68
00:07:09,860 --> 00:07:13,250
So now we need to sort this table.

69
00:07:13,520 --> 00:07:15,230
Every table we need to sort.

70
00:07:17,500 --> 00:07:23,560
And so how we can do that, we need to add one more column.

71
00:07:25,400 --> 00:07:28,430
Ed column and custom column.

72
00:07:29,930 --> 00:07:31,340
And here we have to add.

73
00:07:37,150 --> 00:07:47,080
The table, this is the name of the column and we need here sort of out sort.

74
00:07:57,360 --> 00:08:00,870
And the name of the table is second.

75
00:08:01,810 --> 00:08:09,700
So this is the name of the table which column we need to sort this out, so it will be, I think, Questrom.

76
00:08:13,360 --> 00:08:15,390
That's it, look, OK.

77
00:08:18,580 --> 00:08:20,220
It's a broader underscore key.

78
00:08:26,180 --> 00:08:27,980
Product from this clunky.

79
00:08:31,080 --> 00:08:35,280
So here you can see if I click here.

80
00:08:37,260 --> 00:08:40,440
It is order in ascending order, right?

81
00:08:40,490 --> 00:08:48,060
Everything is in ascending and we need to do this order by descending, so.

82
00:08:49,510 --> 00:08:56,980
Let's go to the city and hear, if I try to add the third element is that it is not possible because

83
00:08:57,160 --> 00:09:03,940
the table doesn't only need two parameters the first time, which is the table and the other parameter

84
00:09:03,940 --> 00:09:04,820
is the criteria.

85
00:09:05,920 --> 00:09:08,050
So we have to use here as a list.

86
00:09:11,710 --> 00:09:21,430
And the first one is the product key and the second one will meet the criteria for order function.

87
00:09:23,790 --> 00:09:25,770
Auchterarder sending this one.

88
00:09:28,000 --> 00:09:30,070
And close the curly bracket.

89
00:09:34,570 --> 00:09:36,760
So know, we need to click, OK?

90
00:09:38,810 --> 00:09:47,780
So here you can see Citified 38, 22, and then the four components, it is 70 for the 18 and so on.

91
00:09:47,960 --> 00:09:57,830
So now we are able to sort everything out and not the next function is we need to get the top two records

92
00:09:58,880 --> 00:10:00,320
so we can do that.

93
00:10:02,690 --> 00:10:13,160
We need to add one more column, custom column, and it is first table talk first, and this is the

94
00:10:13,160 --> 00:10:19,410
function and it takes the first parameter is a table.

95
00:10:19,820 --> 00:10:21,440
This is the product table.

96
00:10:23,850 --> 00:10:28,560
And then we need that, how many euros do we need?

97
00:10:28,740 --> 00:10:29,790
So this is the first.

98
00:10:30,800 --> 00:10:32,920
Turow's, was it?

99
00:10:33,910 --> 00:10:34,480
OK.

100
00:10:35,540 --> 00:10:36,620
And here you can see.

101
00:10:37,610 --> 00:10:50,000
For the components we are getting, 65, 38 and 43, for the bikes, we are getting 65, 38 for the

102
00:10:50,000 --> 00:10:53,260
components, we are getting 70, 40.

103
00:10:53,600 --> 00:11:01,600
And if you look at the product level, so he gives me 70, 40, and in the clothing we have 16 and nine.

104
00:11:01,610 --> 00:11:06,140
And if I click here, the new column is 16 and nine.

105
00:11:07,220 --> 00:11:11,230
So Knottier Wideman's has almost fulfilled.

106
00:11:12,050 --> 00:11:18,440
So what we need now, we need to remove this column, the product label.

107
00:11:19,930 --> 00:11:27,760
And we need to expand and in this column, we need subcategory and the product key.

108
00:11:30,460 --> 00:11:42,010
This is subcategory, and it is you can rename it like a product called so far the we are getting on

109
00:11:42,130 --> 00:11:46,720
our bikes, mountain bikes and the total number of products are certified.

110
00:11:46,720 --> 00:11:51,230
And 38 and the same component are our friends, mountain friends.

111
00:11:51,820 --> 00:11:59,530
So this is the way we can get top products by sell our products by account.

112
00:11:59,680 --> 00:12:02,770
We can do anything what we need.