Aggregate Functions in SQL
Tags: postgres, sql, datascience, database
Published: 2021-11-27 17:46:27

Aggregate functions are mathematical computations that return a single value from a range of values which expresses the significance of the aggregated data. They are used to derive descriptive statistics and provide key numbers in different sectors like the health, economic, and business sectors.
The diagram below shows the typical operation on an aggregate function on a specific column and what the result looks like.

![Image showing  the application of aggregate functions visually](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1xb8ezjif1z2r5zvll2x.PNG)

In the diagram above we can see the dataset with two columns (column 1 & column 2). Using the SUM function on column 2 we can see that it adds up all the values in that column and returns a single value in the "result" column.
For this article, we shall be using PostgreSQL and Pgadmin4 as the GUI which is one of the best Graphical user interface platforms for PostgreSQL and is very beginner-friendly. You can download it [here](https://www.pgadmin.org/download/) for your PostgreSQL needs.

Getting back into it, the different aggregate functions are:
**SUM**: adds up all the values of a specified column.
**MIN**: the minimum value of a specified column.
**MAX**: the maximum value of a specified column.
**AVG**: the average number of values of a specified column.
**COUNT**: the number of values (rows) of a specified column/table.

Aggregate functions can only be used in the SELECT  and HAVING clause where:
The **SELECT** clause lists or specifies the column that will be returned for the SQL query and 
The **HAVING** clause specifies a search condition for a group or an aggregate.

I created a dataset of movie downloads for this article which contains certain movie names, genres, and the number of downloads. This can be created using the SQL statement below:

{%gist https://gist.github.com/Onyi20/050d52ba8445dddd864c950027a6ae51 %}

![article_movies: a dataset created for this article](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vs0t5slqrv6egswekhbt.PNG)

Using the dataset above, we will look at the applications of the different aggregate functions.
🔥Let's go!!

###COUNT()
The COUNT function is the most straightforward function and the best to start with:

{%gist https://gist.github.com/Onyi20/8c8edde3d0bd641feebc7f752b02a710 %}

The "COUNT(*)" in _line 1_ above is used to count all the rows in the dataset. This gives the result in the image below:

![count_aggregate_function_1_output](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aetm1zn0ueq1utbu7srr.PNG)

However, when the COUNT function is used on a column, it counts only the values in that column that are, **not NULL**:

{%gist https://gist.github.com/Onyi20/4ac2e550aad3dd866bd3d2a0c69ff348 %}

looking at the *count_aggregate_function_2* snippet of code above and the *count_aggregate_function_1* snippet before that, we can see that the only difference is the "genre" column which is in the COUNT function and not "*" which denotes all the columns in the table.
The result as seen in the image below is not the same as the COUNT  for the entire table because that column contains two NULL values:

![count_aggregate_function_2_output](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cb657lq07v60yvwipr86.PNG)


Apart from the COUNT function, **all the other aggregate functions are only used on one column at a time**. Following this, let's look at the other functions!!

###SUM()
The SUM function was used to illustrate aggregate functions visually in the image at the start of this article, so it's pretty clear that it adds up the values of a column. Unlike the COUNT function, the SUM function can only be used on columns with a numeric data type:

{%gist https://gist.github.com/Onyi20/d78670ed79f6a3da42bc1df1d8031b1f  %}

![sum_aggegate_function_output](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9ntkqrm4z9fguw6mvphh.PNG)

From _line 1_ in the code snippet above, we can see that the SUM function is applied to the downloads column (a numeric data type column).

###AVG()
The AVG function gets the mean of all values of a specified column. The mean of a set of numbers is the sum of all the numbers in that set divided by the number of values (count) in the set.
Same as the SUM function, the AVG function can only be used on  numeric columns:

{%gist https://gist.github.com/Onyi20/bba309d891fe71bf14490cfb3dea458d %}

![avg_aggegate_function_output](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y3kdird4vwms8vp03u0y.PNG)

###MIN() & MAX()
The MIN and MAX functions are opposites of the same coin in that the MIN function gets the lowest value of a specified column and the MAX function gets the highest value of a specified column. Unlike the other two functions above, the MIN and MAX functions can be used on columns with numerical, date-time, and even character/string data types as seen below:

{%gist https://gist.github.com/Onyi20/e2caacc8727389008b09104e5dfd23c8 %}

![min_and_max_aggregate_functions_output](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5pkfigk7qdxiukuxbi37.PNG) 

Let's take a look at some helpful clauses - AS, GROUP BY and ORDER BY clause.
Take a look at this code below:

{%gist https://gist.github.com/Onyi20/2d542dd2a44c15f596e9b19abdd0bab0 %}

![count_aggregate_function_3_output](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dj1fpnp0lna5q4mqwm3o.PNG)

The result for the snippet of code above is confusing without the code.

Now look at this one:

{%gist https://gist.github.com/Onyi20/fb7fbbe11140f9cb166892fd6895e20f %}

![as_command](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v1x4jqa2s2fz6bcoh7mp.PNG)

This one is better, isn't it? 😉
The **AS** command is used to rename a column or table with an alias (which only exists for the duration of the query). 
The result for the snippet is easier to understand with the AS command added in. This can be used for all sorts of queries to make your output easier to understand.


There are cases when aggregate functions *does not return a single value per column*:

{%gist https://gist.github.com/Onyi20/42f3451c2c1971097e1a6b5de67c6aa2 %}

![group_by_clause](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uzk78ljeh8fi7dqmpthi.PNG)

In *line 3* above, the **GROUP BY** clause is introduced. It groups the SUM of the downloads according to the different genres. 
The GROUP BY clause groups rows with the same values into summary rows. It is used on categorical columns.
Now let's take a look at the URDER BY clause:

{%gist https://gist.github.com/Onyi20/a8550a6f20e0c00fb236d26f6d4e4290 %}

![order_by_clause](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/luip1ixexnvbkzwhj5d6.PNG)

The **ORDER BY** clause is introduced in *line 4* above. It is used to order the output of a column(s) in a table in either ascending (ASC) or descending (DESC) order.

###HAVING()
The HAVING clause is used as a conditional statement for aggregate functions or/and arithmetic. It is used with the GROUP BY clause to filter groups or aggregates based on a specific condition(s).
It is very similar to the WHERE clause to filter/restrict the results of a query. However, unlike the WHERE clause, it can only be used with the SELECT statement and must be used with the GROUP BY clause.

 In this case, we will see how aggregate functions are used to filter a table using the HAVING clause:

{%gist https://gist.github.com/Onyi20/43e99813793c45a6541415af0b817b62 %}

![having_clause](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mekjrpai154x41d30cyi.PNG)

In the snippet above, the GROUP BY clause returns the rows grouped according to the "genre" column and the **HAVING** clause specifies the condition to filter the groups.


Now let's dive into using aggregate functions as window functions.

## Aggregate Functions in Window functions
Window functions are functions that perform operations across a set of rows that are related to the row the function is currently operating on. There are different window functions and they are used to simplify complex operations.
To understand the different window functions and how they are used in SQL, check out [Window Functions in SQL](https://dev.to/onyii/window-functions-in-sql-part-1-lin).

In this article, we shall look at window functions and aggregate functions. 
All the aggregate functions can be used as window functions and they each give awesome and unique results depending on what you are looking for.
Let's look at the **SUM()** as a window function that gives running totals:

{%gist https://gist.github.com/Onyi20/59dbbdd8357f56f7636b680f1335c643 %}

![sum_aggregate_window_function_output](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7yumpzzrwb5tbn4ky8eq.PNG)

The aggregate window function was used to get the running totals for the number of downloads per genre.
* In _Line 1_, all the columns were selected because aggregate window functions do not return a single value as a result. They behave completely like window functions whilst retaining their computational qualities.
* _Line 2_, is where the aggregate window function **SUM() OVER()** is introduced as a brand new column named *"genre_running_total"*. 
This new column is a running total on all the downloads that are split into partitions by their genres and ordered by both the name of the movies and their genres.

Using Aggregate functions either on their own (SUM(), COUNT(), e.t.c) or as a filter (with the HAVING clause) or as a window function (SUM() OVER()) gives different results. 
They are very useful and make SQL coding and data presentation as well as analysis a lot easier.

I hope this has answered some of your questions and given you some new ideas!!
I'll be Back 😎
Bye for now.