PostgreSQL Basics: Aggregate functions (SUM, AVG, etc)

In this article, I’m gonna show you different Aggregate functions in PostgreSQL. Basically, aggregate function are those function which performs a specific task on columns and then produce one aggregate result inside a row.

We have different aggregate functions e.g. COUNT, SUM, MIN, MAX, AVG. We’re going to explore each of them one by one.

Table Of Contents show

PostgreSQL Aggregate Functions:

I have the following table in PostgreSQL Database, and We’re going to query all aggregate function on this table.

Above table name is movie_revenues and we have almost 53 rows of data in this table.

COUNT() Function:

In Postgres, we have a COUNT() function that we use to count no. of rows in a table. COUNT() function takes argument of column and then return no. of rows in that specific column.

Syntax of using COUNT function:

SELECT COUNT(column_name) FROM table_name;

Now let’s query to get how many rows available in movie_revenues table

SELECT COUNT(*) from movie_revenues;

You can also use the COUNT function on a single column to get how many rows available in that specific column. Remember, It will exclude all the data in a column with null values. So, let;s perform COUNT function on domestic_revenues column.

SELECT COUNT(domestic_takings) from movie_revenues;

And now this time, It will return 48 result in one row.

SUM() Function:

You can SUM() function on only a single column and that column should have a data type of either integer Or numeric. Basically, SUM() function collects all data of a column and then returns the sum value of all those data into a single row.

Syntax of SUM() Function:

SELECT SUM(column_name) FROM table_name;

For instance, let’s calculate and add all the data of column domestic_takings. You can perform SUM function on domestic_takings to get total of domestic_takings.

SELECT SUM(domestic_takings) FROM movie_revenues;

You can also use SUM function with filter, like, If you want to add only those data which value is higher than 100.0 then you can use WHERE clause with it.

SELECT SUM(domestic_takings) FROM movie_revenues
WHERE domestic_takings > 100.0;

MIN() Function:

Min function is used to find the minimum value available in any column. Let’s me show you first syntax structure of MIN() function.

Syntax Structure of MIN() Function:

SELECT MIN(column_name) FROM table_name;

Now, let’s find minimum domestic taking from domestic_takings column.

SELECT MIN(domestic_takings) FROM movie_revenues;

MAX() Function:

Max function is used to find the maximum value available in any column. Let’s see the structure of MAX() Function.

Syntax Structure of MAX() Function:

SELECT MAX(column_name) FROM table_name;

Now let’s find out highest domestic taking from domestic_takings column.

SELECT MAX(domestic_takings) FROM movie_revenues;

AVG() Function:

AVG() is used to calculate average value of all data inside a particular column. Now let’s see syntax of AVG() Function:

SELECT AVG(column_name) FROM table_name;

Now, let’s perform it on domestic_takings column to get an average value of domestic takings.

SELECT AVG(domestic_takings) FROM movie_revenues;

These were the some important aggregate functions, I hope you liked this article.

So, if you liked this article then please don’t forget to share this with your friends. You can also subscribe to our blog via email to get future notifications from our blog.

Thanks to read…