Original subtitles

Now the next aggregate function is count function.

As the name suggests, Count will add a column with the number of rows within the window frame.

For example, if we have customer and store and we want a new column which stores the information of

how many customers each store have, then we can use count.

It is very similar to average just inside of calculating average.

It is calculating the count of rows.

So here in our data, we have three customers in store A we have four customers in the store.

B So for all the customers who are from a story, we want three and for all the customers who are from

a store.

B, we want value as four.

So this is the final table we want for C, one, C five and C seven.

We want value as three since there are three customers in the story.

And for C, two, C, three, C, four and C six.

We want the value is four since there are four customers from store B.

Now let's go to our pkg admin.

So now let's create a column with a total number of customers from each estate.

So we want select.

Because somebody.

Customer name.

Cuomo.

Estate.

Then we wanted to count off customer IDs in each state.

So count is our function and bracket.

I have to write what I want to count.

So here we are, counting the customer IDs in each state.

So we'll write customer.

Heidi.

In this case, you can also write customer name instead of customer ID, It will give you the same result.

Then we will write the over keyword and in bracket we'll write partition by.

And then we'll write estate.

Since we want to partition our data and calculate count in each state.

Then we can give Alias and we'll give an alias of count of customer count cost.

And we want this data from our customer order table that we created in our previous lectures.

I think our query is ready.

Let's just run this.

You can see that from Alabama.

We have one, two, three, four, five, six, seven, eight, nine customers.

That's why in front of every customer who is from Alabama, we are getting nine.

And Arizona.

We have 21 customers.

So for each customer we are getting 21.

In this column.

In California, we have around 161 customers.

Similarly, in Michigan, we have 16, so all the customers who are from Michigan have 16 value.

And this column.

So that's how we use count window function.

In the next lecture we'll see how to calculate sum or total.