SUM() in Window Functions

Window Functions perform computation over a set of rows called windows and return an aggregated value for each row.
Aggregate Functions are used to return a summarised value of multiple rows that make some mathematical sense. You can use aggregate functions as Window Functions with the help of the OVER() clause.

There are 5 types of Aggregate Window Functions:

• SUM()
• AVG()
• COUNT()
• MAX()
• MIN()

In this notebook, we will look at one of the aggregate window functions called SUM(). SUM() as an aggregate function is used to return the total sum of a stated numeric column. When used as a window function, it will still return the sum of a specific column but instead of returning a single value, we can return the running sum at each row/window.

For grasping this better, I've considered using the Classic Models database and will be working with MySQL workbench. You can follow the steps below to set up this database locally:

1. Download this SQL file
2. In MySQL Workbench, click "File" > "Open SQL Script" to open the script;
3. Execute the script to create and populate the database.

Once executed, you should be able to view and browse tables in the "Schema" section of the sidebar. If you face an error, make sure you have a MySQL server running.

About the Database:

Classic Models Inc. is a distributor of small-scale models of cars, motorcycles, planes, ships trains, etc. Products manufactured by Classic Models are sold in toy & gift stores around the world. Here's a small sample of their products (source):

Classic Models has offices around the world with dozens of employees. The customers of Classic Models are typically toy/gift stores. Each customer has a designated sales representative (an employee of Classic Models) they interact with. Customers typically place orders requesting several products in different quantities and pay for multiple orders at once via cheques.

Here's the Entity Relationship Diagram (ERD) for the database:

Let's understand this with the help of a sample question/query:

Question: Calculate the running total of payments made by a customer.

Below is a screenshot of the payments table:

SUM as a Window Function

We can simply implement a window function using the Over and Partition clause.

OVER indicates to SQL, that we are now implementing a Window Function. And PARTITION BY divides the table into windows by specified column.

1. To answer our query about calculating the running total of the payments we will create Partitions based on customerNumber.
2. Further to arrange them, we can use ORDER BY. In the query below I have ordered my result by paymentDate.
3. Finally, I've used SUM() to the resultant windows which is being applied row-by-row.

SUM as an AGGREGATE Function

Below is the result of SUM() as an aggregate function that uses GROUP BY to return summarized values of groups.

Conclusion

Window Functions prevent rows from getting grouped into a single resultant row, as we saw in the example above, and allow rows to retain their separate identities. You can use Aggregate Window functions to perform aggregates on a row level, without a GROUP BY statement.

You can follow up on other Aggregate Window Functions like AVG, COUNT, etc.. in upcoming articles.