PostgreSQL Fundamentals 3 : Aggregate functions

In the previous chapter, many filtering methods we’ve looked into. In this chapter we’ll be focusing on using of aggregate functions. An aggregate function is to perform a calculations on a set of values to return a single value.

List of aggregate functions in PostgreSQL can be found here. We’ll be focusing on few which are used often, AVG(), SUM(), MIN(), MAX()

Let’s calculate total asset of all people’s asset

SELECT SUM(asset)
FROM people;
sum
----------50320211(1 row)

Get average of all people’s asset

SELECT AVG(asset)
FROM people;
avg
--------------------50320.211000000000(1 row)

Get the asset of highest asset

SELECT MAX(asset)
FROM people;
max
-------99967(1 row)

Same for Minimum value

SELECT MIN(asset)
FROM people;
min-----27(1 row)

Let’s find maximum asset who were born in between ‘2000–01–01’ and ‘2003–12–31’:

SELECT MAX(asset)                                                                                          FROM people                                                                                                       WHERE birt_date BETWEEN '2000-01-01' AND '2003-12-31';max-------96482(1 row)

How about average asset of people who’s name starts with ‘R’ :

SELECT AVG(asset)
FROM people
WHERE first_name LIKE 'R%';
avg--------------------51911.492307692308(1 row)

Let’s calculate average asset of people who’s from France and name starts with “R” make alias average as avg_r:

SELECT AVG(asset) AS avg_r
FROM people
WHERE birth_country = 'France'
AND first_name LIKE 'R%';

avg_r
--------------------60002.000000000000(1 row)

In the next chapter we’ll be looking into sorting and grouping.

https://www.postgresql.org/docs/current/functions-aggregate.html

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store