An aggregate operators are functions that operate on the multiset of values of a column of a relation, and return a value. Aggregation functions include:
To see the queries in action, try our demos below:
To get the age from birthdate column, since we did not have a column for users, we dynamically calculate the age based on the current time. PostgreSQL comes with a helper called AGE() that gets the time interval between now and the date. However, this returns as a Postgres Interval type, so we use EXTRACT to get just the years.
SELECT EXTRACT (YEAR FROM AGE(birthdate)) AS age FROM users;
To get the average age of all users, we used what we found from the query above and used the aggregation function AVG.
SELECT AVG (EXTRACT (YEAR FROM (AGE (birthdate)))) FROM users;
This query can be changed to get the minimum or maximum age, or total ages by replacing AVG with MIN, MAX, or SUM.