Original subtitles

Now the next function we are going to discuss is total function.

Total also works works like count and average function.

It will create a new column containing a sum of values within a window frame or within your partition.

So again, suppose we have two different kinds of customers C one and C two.

Now C one and C two are placing multiple orders at different dates.

And we also have the order value with us.

Now, if I want to calculate the total order value of C one and C two to be placed in front of each

row, I can use the total function.

For example, if C one is ordering three times with the revenue of 100, 203 hundred, I want a total

of 600 to be written in front of all the rows of C one.

So on first of Jan 2022, 11th of May 2022 and 25th of August 2022, I want 600 return to be in front

of each of this row.

Similarly, C two is placing order four times with value 300, 300, 204 hundred.

The total is 1200.

So I want 1200 to be written in front of each row of customer C to.

Now how to do that?

We can use the some window function.

We just have to write some and then bracket.

We have to write on what column we want the sum to be calculated.

So here we want the sum on revenue.

So we have to write revenue and again, we have to write keyword of over and partition by and partition

by.

We have to specify how to group our data for calculating this total.

So here in this case we are partitioning on customer.

So we have to write customer.

Now let's calculate the total of each state in front of each order id.

So I want a table which contains order IDs date of that order, and then a state from which that order

was placed, and then another column of total value of orders from that state.

So let's see how to do that.

Now first, let's see the order table.

So I'll write total.

You can ignore this.

This is just the commenting part.

So select the star from.

So it's first, let's understand this table.

Here we have a unique key of order line.

Then we have order I.D. and then order date corresponding to that order ID.

We also have customer ID corresponding to that order ID.

We don't have the state data right now.

And we also have the sales data here.

So now first what we are going to do is we will roll up this data on order ID level.

So currently order ID is not unique.

There can be multiple rows of same order ID.

This is happening because if the product ID is different, we are getting multiple lines of that order.

ID is split ID according to the product ID since we only concerned with our revenue and customer data

here, will roll it up to order ID.

So how to do that?

Select order ID.

Then we want to date.

Date is unique to authority, so it will always be same within each order.

So we also want order date here.

But since we will be grouping this data on order ID, we have to use the aggregate function on order

date.

So in such cases you can just use max order function.

It will do nothing.

It will just get the maximum date out of this order.

I will name it as order date only.

I also want customer ID and I also know that there is only one customer ID belonging to an order ID

There can be multiple customer IDs for a same order.

But again, since I am grouping this data on order ID only have to use the aggregate function.

Again, I will be using a max aggregate function.

Max also works on a string type of data.

It will give you one of the multiple values that you have for customer ID.

I'm just using Max of customer ID as customer ID.

And then most importantly, we want the total sales.

So we'll write some off sales.

As revenue or.

As.

Sales.

From.

Sales stable.

Let's run this.

We forget to add group by close group by.

Moderately.

Let's run this.

Now you can see that my data is rolled up to other ID level.

I have the order ID and the sum of sales from that order ID.

I also have ordered it and customer ID of that order.

Now let's store this data into a table called.

S order to roll up.

Here we have the order ID or the date customer ID and the total sales from that order.

Let's create this.

So we have created this order rollup.

Now, let's add the information of state in this order.

Rollup table.

So we will write Create table order.

Roll up.

The state as.

Select will put this order rollup table as our left table or a table and the customer table as our B

table will write a dot star comma B dot state.

From.

Order.

Roll up.

As a.

Left.

Join.

Customer.

Yes.

Be.

On.

He dot.

Customer ID.

Equal to B dot customer ID.

Let's run this.

You can see that our query is successful.

Let's look at our order rollup.

State table.

I will correct this name as well.

Let's run this again.

Yeah.

Lets view the data.

Select start from this.

If I run this, you can see that.

Now we have the estate column as well.

And this data.

Now, as I said, we wanted to have another column here which will contain the total sales value of

this state.

So how can we do that?

We can select.

A star, comma.

We'll write some.

This is our aggregate window function.

Now we want the sum of sales.

Sum of sales we have to write over.

Or what is our keyword?

Then bracket is start.

Partition by is another keyword.

Now here, since we want to calculate the sum of all the sales in a particular estate, so here I will

partition by estate.

Now we have to give Alias.

We want to save this column as sales.

State.

Total.

From.

You want this data from this table.

Now let's run this.

You can see that now we have the sales.

Total of each state.

For example, for Alabama, we have these many other IDs.

And the total of sales from Alabama is this one 31,038 units.

Similarly, we have data of other states as well.

So that's how we can use some Windows function and SQL.