In this video, we will do a very small project where we need three column, the first column will have
the product categories and in the second column we need comma separated subprojects.
And in the third column, we need the total number of products available for these categories.
So how we can accomplish this Allport with the help of poverty language.
So let's go to the poverty editor and in the last four days, you have seen how we have much more product
category and products.
So we need to do the same thing here.
The products category.
Mouskouri muscaria as new.
And here in your product category, this primary, and then we need to select product category and the
foreign key here is product category.
And here we need to sit in a joint click.
OK, so this table contains all the columns from the subcategory table that belong to the product category.
So we can name this table as a category as.
And which column do we need?
We don't need a product category and.
Product category, so we can remove.
And we don't need the French product and Spanish, so we need to delete these things so we are invested
in the product category name and the other information from the subcategory table.
So we need in this broader subcategory, we need products, subcutaneously, because we will use subcategory
to get the related products on the product table.
And we need English, a subcategory name here.
You can uncheck use original column name as prefix and then you can click OK.
And here you can see this is the broader subcategory and this is the English border subcategory.
Now we can short this name like a subject.
Great name.
And so we have this table now, we need to match this table with the product table to get the product
information.
So for this, we need to click here, Mouskouri as new
and then we have to select your products.
Tabin.
The city of.
Here we have the six day primary is the broader subcategory, and then we need the product subcategory
key here in the product Labor.
So and here you can select the inner join me so you can see better.
So that's all.
And now we need to click, OK?
So here you can see we are getting English product, category, name, subcategory, key, subcategory
name and this product information, if you click here, you can see the color from the product table.
So we are not interested in all the columns from the product table.
So we are only interested in the in the product key so that the product key, so that we we can count
these products.
So for this we need to click add column custom color.
And here again, OK, let me change the name here so we can say so final, a final table, for example.
This is the name at the table, so we have to do everything here.
No, no, we have a product category, name subcategory and the product information.
So all the three things, as you have seen here, the product name, the category name, subcategory
and product information.
So we have everything available in the final table.
So we need to add one column here because some column.
And here we have to write, for example, products.
For our products underscore key for them, this is the name of the column, and then we have to choose
one and function select.
Tabletop column.
And we I don't remember the name of the column that we need, so let me click here.
So it's a product, keeps capital and keeps capital.
So we need to add custom custom column.
And the name of the column is Product on Cookie.
And here we have to write the name of the function that a select column.
The first parameter is, is the table.
So what is the table?
The table is the products.
This this column this is the column that contains statements, so and then the second column we need.
Little quotations and we have the right product.
OK, that's it, because we are only sitting in one column and then we have to step in it so you can
see it better.
So this is the whole stop and then we need to click.
OK, so here you can see if you click.
Here you can see the available products, keys that belong to the subcategories, right, because we
have a relationship between Category two subcategories, subcategory to product.
So we don't need this column product so we can remove it and we don't need a product category, product
category subcategory.
We can remove it.
And we have the product category, name, subcategory name and product information.
So here we can add one more column.
Some call them and.
Then write one function here is like a role count table Dutrow Count, and it needs one table.
And the table is our table is available under poder and discothèque who can so that it can count the
number of rows.
OK.
And here you can see.
For example, this table, this table contains these rules, right, and this function is counting these
rules for the are 38 values available in this column.
Against this bike.
OK, so now we don't need Brodkey table so we can remove it and now we have of like under bike, we
have mountain bikes and a number of products that belong to a mountain.
Bikes ought to be eight or bikes 65 and so on.
We need a groupie so we can click on.
We can click on, like, transform and grow by.
Here we have to choose which column we need, grouping on the product category, name of the separate
category name.
And you can change it and you can write any any name here.
And here we have the tools, Oros.
Yes, it.
So because we need all the rules against.
English product category Ghaleb.
So you can click, OK, now we are quite near to the airport in which we are interested.
So here you can see we are having bikes, component clothing, accessories, and now we need these two
rows.
Right.
So like here for the mountain bike, there are one twenty five.
And if you click here, buy.
So if we add 38 plus testify plus 22, we will get the same amount.
One twenty five.
So we will do now step by step two to get this output.
So now we need subcategory name, so for this, we need to use our list.
And.
For this, we have to add new column because some column.
And here we can invite subcategory list and here it will be column.
Column is a function for like David D'Arte column.
This one.
And the dysfunction, the return of alist.
So here we have to provide one table and what is the table here is the name of the table is comped and
which column in which we are industrial interest in the subcategory name.
So it is a subcategory named.
At the subcategory, that's it, and I want you to click, OK?
So we are getting on Ed Sagat name.
The table was not found, they is some problem in the name.
It's a subject I think is scripted, so very sensitive language.
And as his captain this week, OK, and here you can see we are getting one list that contains mountain
bikes for bikes and the dooring bikes.
And we need one more thing here.
We do have to click on the exact values.
And here you have to you can choose any separator here according to the required output we need, comma,
so you can click comma here, click OK.
And here you can see so here you can see we are we are able to get almost the same output, the English
product category name and the subcategory named the concatenated subcategory list with the commas with
a comma separated list.
So not last point is we need the product count, so for this, we need to add one more column here.
This is the product found, and first we need to list, uh, table column.
Table column.
And the name of the table is because we are interested in it, because the table is available on the
counter.
So this is the name of the table and in which column we are interested.
Lecoq.
So here you can see we are getting the list of products, right, so we won't know we need to sum these
numbers.
So we have to add one more call.
Are you can do one more thing if you don't need to call them again and again, you can just go to the
ED column three setting, because this whole thing is returning us a list so we can use one list function
here.
So it is some list or some.
And the list of some needs a list as a parameter, so we can just close it.
And now we can click, OK, so here you can see.
We are able to get the number of products, so we are not interested in the second column.
We can remove it.
And here you can see the product category, name, subcategory name and the product count.
So if we compare both output, so here you can say the English product category name.
This is the subcategory name and this one is it product count.
So you have seen how easy it is to get the required output.
You can prepare data set according to your requirement.
If you know the theory behind the latest table functions grouped by and if you follow the proper flow,
you can prepare any data set for your ongoing dashboard or your infa for your projects.