Original subtitles

So a sales manager is back again and he's got some further questions for us.

So I'm going to go back to my activity page.

I've deleted the previous tables.

We're just going to start with a blank page.

So our first question is, is creates a new table displaying the sales by product name and they're going

to sort the table from highest sales to lowest sales.

We want to know which product has the highest sales.

So we're going to use a table for this one.

Just move it across.

We want to see our product name.

We want to see sales.

Now we've got quite a lot of product names.

Again, we could just use our sort so our highest and we can see that the Qantas projector 1080p x 90,

well non ATO Black has the highest sales.

We also need to know which sales is the lowest.

So again, we can just change our sort order and we can see that that is a V USB data cable.

E 600 gray.

Okay, so there we go.

Those are the first two questions.

I'm going to actually delete this table.

Let's just move on to a new one.

Which product is sold in the most cities?

Again, let's use a table for this.

So in this case, we want to know the list of product names again, but we want to know the number of

cities.

So we want to know for each of the product names how many cities it's got.

So we want to drag the cities and also drop it in there.

So we'll get a text list of all the city names, but that's not what we actually want in this case.

We want to know what is the distinct count, because you remember, we want to then count each time

that a city is there.

We want to know which product name is actually sold in the most cities.

So in this case, when we do highest to lowest, it tells us that the lit way home theater system is

actually sold in the most cities.

So it sold in 22 cities.

Okay, Let's move on to the next question.

So for this question, we're going to be looking at a new table and we're going to show our sales by

country.

So again, let's just delete this one.

The new table.

And we just want our sales by country.

Pop our country in their sales.

And in this case, we want to know average which country has the highest average sales.

Okay.

Right.

So we're going to need to change our method of aggregation.

We're going to drop down, going to go to average, and we want to then change the sort order on this

so we can see that China actually has the highest average cell at $4,518.

The next one is which country has the lowest minimum sales, Right?

So we need to change this again and we're going to go to a minimum.

And in this case, though, we want to know the lowest.

So we're just going to change the sort order again so we can see the China, Singapore and United States

all have a value of five there.

So basically all three countries has a $5 minimum.

And we want to know which country has the highest maximum cell.

So this again, change our method of aggregation back to maximum and just sort order.

And then we go.

We can see the United States had a sale of $78,312, which was the highest individual sale.

Okay, moving on.

We're going to create a new table.

In this table, we're going to have the profit by city.

So let's delete that again.

Another table.

Get our city in there.

Let's get a profit it in there.

Move this in a little bit.

Right.

So what's the questions we need to answer?

We need to know which city has the highest number of transactions.

So in this case, we're going to be using the count because we just need to know the count of the rows

that are in the data set.

So we're going to go into our count.

This is going to give us the count of the rows.

And again, we want to know the highest.

So in this case, it's telling me that Beijing has 1398 rows that are in the data set.

So there's the highest number of transactions.

Next one is which city has the lowest average profit?

So again, we're going to change this.

So we want to now know what is the average.

And we want to know what is the lowest.

And we can see in this case it is Venetia has an average profit of $438, which is the lowest.

The last question Which city sells the most different types of products, i.e. which city sells the

most different types of product names?

Right.

So we're going to keep city, but we're going to be removing our profit for this one.

So we want to bring our product name back in and we want to be doing a distinct count again on a product.

So now for each city, it's telling me how many product names that city actually sells.

And we want to know which ones got the most.

So again, we can use our sort and you can see that Beijing actually sells the most types of product

names.

924.

So what that's saying is basically that Beijing has sold 924 different types of product name.

Okay, So hopefully our sales manager is going to be happy with those results.

We're going to conclude the activity and I will see you in the next lesson.