1
00:00:00,330 --> 00:00:06,360
In this video, we will do a very small project where we need three column, the first column will have

2
00:00:07,170 --> 00:00:12,630
the product categories and in the second column we need comma separated subprojects.

3
00:00:12,630 --> 00:00:18,180
And in the third column, we need the total number of products available for these categories.

4
00:00:19,230 --> 00:00:24,630
So how we can accomplish this Allport with the help of poverty language.

5
00:00:24,840 --> 00:00:31,500
So let's go to the poverty editor and in the last four days, you have seen how we have much more product

6
00:00:31,500 --> 00:00:32,690
category and products.

7
00:00:33,270 --> 00:00:35,220
So we need to do the same thing here.

8
00:00:35,460 --> 00:00:37,070
The products category.

9
00:00:38,120 --> 00:00:39,860
Mouskouri muscaria as new.

10
00:00:41,550 --> 00:00:48,810
And here in your product category, this primary, and then we need to select product category and the

11
00:00:48,810 --> 00:00:50,800
foreign key here is product category.

12
00:00:51,720 --> 00:00:54,900
And here we need to sit in a joint click.

13
00:00:54,930 --> 00:01:02,520
OK, so this table contains all the columns from the subcategory table that belong to the product category.

14
00:01:03,360 --> 00:01:07,620
So we can name this table as a category as.

15
00:01:12,440 --> 00:01:15,230
And which column do we need?

16
00:01:16,040 --> 00:01:18,950
We don't need a product category and.

17
00:01:20,900 --> 00:01:23,870
Product category, so we can remove.

18
00:01:24,970 --> 00:01:31,720
And we don't need the French product and Spanish, so we need to delete these things so we are invested

19
00:01:31,730 --> 00:01:37,620
in the product category name and the other information from the subcategory table.

20
00:01:38,260 --> 00:01:47,890
So we need in this broader subcategory, we need products, subcutaneously, because we will use subcategory

21
00:01:48,100 --> 00:01:51,330
to get the related products on the product table.

22
00:01:51,880 --> 00:01:54,880
And we need English, a subcategory name here.

23
00:01:54,880 --> 00:02:01,120
You can uncheck use original column name as prefix and then you can click OK.

24
00:02:02,220 --> 00:02:09,120
And here you can see this is the broader subcategory and this is the English border subcategory.

25
00:02:09,150 --> 00:02:12,990
Now we can short this name like a subject.

26
00:02:15,100 --> 00:02:15,640
Great name.

27
00:02:18,650 --> 00:02:27,800
And so we have this table now, we need to match this table with the product table to get the product

28
00:02:27,800 --> 00:02:28,390
information.

29
00:02:29,750 --> 00:02:34,280
So for this, we need to click here, Mouskouri as new

30
00:02:37,010 --> 00:02:39,530
and then we have to select your products.

31
00:02:39,530 --> 00:02:39,890
Tabin.

32
00:02:43,280 --> 00:02:44,000
The city of.

33
00:02:45,870 --> 00:02:54,640
Here we have the six day primary is the broader subcategory, and then we need the product subcategory

34
00:02:54,660 --> 00:02:56,400
key here in the product Labor.

35
00:02:58,320 --> 00:03:04,090
So and here you can select the inner join me so you can see better.

36
00:03:04,980 --> 00:03:06,620
So that's all.

37
00:03:07,350 --> 00:03:09,530
And now we need to click, OK?

38
00:03:15,060 --> 00:03:22,590
So here you can see we are getting English product, category, name, subcategory, key, subcategory

39
00:03:22,590 --> 00:03:28,080
name and this product information, if you click here, you can see the color from the product table.

40
00:03:28,300 --> 00:03:33,850
So we are not interested in all the columns from the product table.

41
00:03:33,870 --> 00:03:40,830
So we are only interested in the in the product key so that the product key, so that we we can count

42
00:03:40,830 --> 00:03:42,120
these products.

43
00:03:42,720 --> 00:03:46,560
So for this we need to click add column custom color.

44
00:03:49,080 --> 00:03:57,320
And here again, OK, let me change the name here so we can say so final, a final table, for example.

45
00:03:57,570 --> 00:04:00,900
This is the name at the table, so we have to do everything here.

46
00:04:00,910 --> 00:04:05,810
No, no, we have a product category, name subcategory and the product information.

47
00:04:05,820 --> 00:04:12,240
So all the three things, as you have seen here, the product name, the category name, subcategory

48
00:04:12,240 --> 00:04:13,280
and product information.

49
00:04:13,290 --> 00:04:16,580
So we have everything available in the final table.

50
00:04:16,680 --> 00:04:19,800
So we need to add one column here because some column.

51
00:04:21,380 --> 00:04:25,070
And here we have to write, for example, products.

52
00:04:27,770 --> 00:04:34,150
For our products underscore key for them, this is the name of the column, and then we have to choose

53
00:04:34,150 --> 00:04:36,040
one and function select.

54
00:04:41,660 --> 00:04:43,110
Tabletop column.

55
00:04:46,950 --> 00:04:55,310
And we I don't remember the name of the column that we need, so let me click here.

56
00:04:56,040 --> 00:04:59,080
So it's a product, keeps capital and keeps capital.

57
00:04:59,340 --> 00:05:02,490
So we need to add custom custom column.

58
00:05:03,240 --> 00:05:06,710
And the name of the column is Product on Cookie.

59
00:05:07,770 --> 00:05:11,970
And here we have to write the name of the function that a select column.

60
00:05:12,180 --> 00:05:14,790
The first parameter is, is the table.

61
00:05:14,850 --> 00:05:15,770
So what is the table?

62
00:05:16,020 --> 00:05:17,730
The table is the products.

63
00:05:19,740 --> 00:05:27,810
This this column this is the column that contains statements, so and then the second column we need.

64
00:05:31,080 --> 00:05:33,960
Little quotations and we have the right product.

65
00:05:34,670 --> 00:05:43,260
OK, that's it, because we are only sitting in one column and then we have to step in it so you can

66
00:05:43,260 --> 00:05:44,040
see it better.

67
00:05:44,550 --> 00:05:47,070
So this is the whole stop and then we need to click.

68
00:05:47,070 --> 00:05:51,030
OK, so here you can see if you click.

69
00:05:52,320 --> 00:06:01,830
Here you can see the available products, keys that belong to the subcategories, right, because we

70
00:06:01,830 --> 00:06:06,090
have a relationship between Category two subcategories, subcategory to product.

71
00:06:06,210 --> 00:06:18,060
So we don't need this column product so we can remove it and we don't need a product category, product

72
00:06:18,330 --> 00:06:18,820
category subcategory.

73
00:06:18,870 --> 00:06:20,460
We can remove it.

74
00:06:21,430 --> 00:06:26,170
And we have the product category, name, subcategory name and product information.

75
00:06:26,290 --> 00:06:28,750
So here we can add one more column.

76
00:06:30,660 --> 00:06:32,790
Some call them and.

77
00:06:36,210 --> 00:06:46,040
Then write one function here is like a role count table Dutrow Count, and it needs one table.

78
00:06:47,390 --> 00:06:56,150
And the table is our table is available under poder and discothèque who can so that it can count the

79
00:06:56,150 --> 00:06:57,470
number of rows.

80
00:06:59,210 --> 00:06:59,530
OK.

81
00:07:00,530 --> 00:07:01,550
And here you can see.

82
00:07:02,930 --> 00:07:10,820
For example, this table, this table contains these rules, right, and this function is counting these

83
00:07:10,820 --> 00:07:16,520
rules for the are 38 values available in this column.

84
00:07:17,900 --> 00:07:19,150
Against this bike.

85
00:07:20,060 --> 00:07:30,710
OK, so now we don't need Brodkey table so we can remove it and now we have of like under bike, we

86
00:07:30,710 --> 00:07:34,340
have mountain bikes and a number of products that belong to a mountain.

87
00:07:34,340 --> 00:07:39,110
Bikes ought to be eight or bikes 65 and so on.

88
00:07:39,340 --> 00:07:42,740
We need a groupie so we can click on.

89
00:07:44,660 --> 00:07:48,230
We can click on, like, transform and grow by.

90
00:07:51,800 --> 00:07:57,890
Here we have to choose which column we need, grouping on the product category, name of the separate

91
00:07:57,890 --> 00:07:58,490
category name.

92
00:07:59,470 --> 00:08:05,710
And you can change it and you can write any any name here.

93
00:08:08,110 --> 00:08:10,840
And here we have the tools, Oros.

94
00:08:12,440 --> 00:08:12,950
Yes, it.

95
00:08:14,160 --> 00:08:17,970
So because we need all the rules against.

96
00:08:19,610 --> 00:08:21,230
English product category Ghaleb.

97
00:08:22,150 --> 00:08:28,610
So you can click, OK, now we are quite near to the airport in which we are interested.

98
00:08:29,290 --> 00:08:37,030
So here you can see we are having bikes, component clothing, accessories, and now we need these two

99
00:08:37,050 --> 00:08:37,450
rows.

100
00:08:37,610 --> 00:08:37,960
Right.

101
00:08:38,440 --> 00:08:42,240
So like here for the mountain bike, there are one twenty five.

102
00:08:42,850 --> 00:08:45,010
And if you click here, buy.

103
00:08:45,460 --> 00:08:51,910
So if we add 38 plus testify plus 22, we will get the same amount.

104
00:08:51,910 --> 00:08:52,810
One twenty five.

105
00:08:54,310 --> 00:08:59,740
So we will do now step by step two to get this output.

106
00:09:02,250 --> 00:09:08,940
So now we need subcategory name, so for this, we need to use our list.

107
00:09:10,030 --> 00:09:10,540
And.

108
00:09:12,210 --> 00:09:15,300
For this, we have to add new column because some column.

109
00:09:17,470 --> 00:09:26,980
And here we can invite subcategory list and here it will be column.

110
00:09:29,600 --> 00:09:35,390
Column is a function for like David D'Arte column.

111
00:09:39,150 --> 00:09:39,690
This one.

112
00:09:42,310 --> 00:09:44,680
And the dysfunction, the return of alist.

113
00:09:48,060 --> 00:09:56,940
So here we have to provide one table and what is the table here is the name of the table is comped and

114
00:09:56,940 --> 00:10:00,780
which column in which we are industrial interest in the subcategory name.

115
00:10:03,990 --> 00:10:07,410
So it is a subcategory named.

116
00:10:08,390 --> 00:10:14,720
At the subcategory, that's it, and I want you to click, OK?

117
00:10:16,390 --> 00:10:19,810
So we are getting on Ed Sagat name.

118
00:10:21,440 --> 00:10:24,650
The table was not found, they is some problem in the name.

119
00:10:33,240 --> 00:10:38,760
It's a subject I think is scripted, so very sensitive language.

120
00:10:40,500 --> 00:10:49,170
And as his captain this week, OK, and here you can see we are getting one list that contains mountain

121
00:10:49,170 --> 00:10:52,710
bikes for bikes and the dooring bikes.

122
00:10:56,290 --> 00:10:58,810
And we need one more thing here.

123
00:11:01,380 --> 00:11:04,650
We do have to click on the exact values.

124
00:11:06,010 --> 00:11:14,590
And here you have to you can choose any separator here according to the required output we need, comma,

125
00:11:15,430 --> 00:11:18,040
so you can click comma here, click OK.

126
00:11:20,780 --> 00:11:27,380
And here you can see so here you can see we are we are able to get almost the same output, the English

127
00:11:27,380 --> 00:11:35,150
product category name and the subcategory named the concatenated subcategory list with the commas with

128
00:11:35,150 --> 00:11:36,650
a comma separated list.

129
00:11:37,630 --> 00:11:45,940
So not last point is we need the product count, so for this, we need to add one more column here.

130
00:11:49,290 --> 00:11:59,010
This is the product found, and first we need to list, uh, table column.

131
00:12:02,600 --> 00:12:03,860
Table column.

132
00:12:13,050 --> 00:12:19,500
And the name of the table is because we are interested in it, because the table is available on the

133
00:12:19,500 --> 00:12:20,010
counter.

134
00:12:20,670 --> 00:12:25,860
So this is the name of the table and in which column we are interested.

135
00:12:28,400 --> 00:12:29,050
Lecoq.

136
00:12:30,230 --> 00:12:38,990
So here you can see we are getting the list of products, right, so we won't know we need to sum these

137
00:12:38,990 --> 00:12:39,410
numbers.

138
00:12:40,640 --> 00:12:43,220
So we have to add one more call.

139
00:12:47,210 --> 00:12:55,190
Are you can do one more thing if you don't need to call them again and again, you can just go to the

140
00:12:55,190 --> 00:13:02,420
ED column three setting, because this whole thing is returning us a list so we can use one list function

141
00:13:02,420 --> 00:13:02,710
here.

142
00:13:03,950 --> 00:13:08,330
So it is some list or some.

143
00:13:11,090 --> 00:13:18,680
And the list of some needs a list as a parameter, so we can just close it.

144
00:13:20,310 --> 00:13:25,410
And now we can click, OK, so here you can see.

145
00:13:26,660 --> 00:13:34,170
We are able to get the number of products, so we are not interested in the second column.

146
00:13:34,190 --> 00:13:35,090
We can remove it.

147
00:13:37,070 --> 00:13:42,630
And here you can see the product category, name, subcategory name and the product count.

148
00:13:43,400 --> 00:13:49,100
So if we compare both output, so here you can say the English product category name.

149
00:13:50,030 --> 00:13:54,260
This is the subcategory name and this one is it product count.

150
00:13:54,440 --> 00:13:59,900
So you have seen how easy it is to get the required output.

151
00:14:00,470 --> 00:14:03,820
You can prepare data set according to your requirement.

152
00:14:03,830 --> 00:14:11,510
If you know the theory behind the latest table functions grouped by and if you follow the proper flow,

153
00:14:12,290 --> 00:14:21,770
you can prepare any data set for your ongoing dashboard or your infa for your projects.