Intro to Aggregate Functions

Aggregate functions... These things are real wizards in the world of data analysis! They help you turn millions of rows into compact and understandable results. With them, you can count, find minimums and maximums, average values, and do a bunch of other stuff. So, let’s break it all down step by step.

Aggregate functions are special SQL functions that perform operations on groups of rows and return a single result. If you want an analogy, it’s like collecting all the stats about your morning coffee, for example: how many cups you drank in a week, whether you added sugar, or how many days in a row you added cream.

Examples of tasks that aggregate functions solve:

• Count the total number of records in a table.
• Find the minimum or maximum values in a numeric column.
• Sum up values in one of the columns.
• Average all the values in a column.
• Pick out unique values.

How does it work “under the hood”?

SQL runs an aggregate function after selecting data in SELECT. For example, when you use SUM() or AVG(), SQL first grabs the data, and then does the calculation only for the selected rows.

Top Aggregate Functions in PostgreSQL

Let’s get practical and check out the top 5 heroes:

1. COUNT() — counts the number of rows.
2. SUM() — sums up values in a numeric column.
3. AVG() — calculates the average value.
4. MIN() — finds the minimum value.
5. MAX() — finds the maximum value.

Examples of Using Aggregate Functions

1. Counting rows with COUNT()

The COUNT() function lets you count the total number of rows in a table or the number of non-empty values in a specific column.

Example: let’s say we have a students table that stores info about students:

Let’s count the total number of students:

SELECT 
    COUNT(*) AS total_students
FROM students;

Result:

Now let’s count the number of students who have a grade:

SELECT 
    COUNT(grade) AS students_with_grades
FROM students;

Result:

Why is that? Because COUNT(column) ignores NULL values.

2. Summing up with SUM()

The SUM() function is used to sum up all the values in a numeric column.

Example: let’s find out how many total points our students scored.

SELECT 
    SUM(grade) AS total_grades
FROM students;

Result:

Note: If there’s a NULL in the column, it just gets ignored in the sum.

3. Average value with AVG()

The AVG() function calculates the average value for all numeric records in a column.

Example: let’s calculate the average grade of the students.

SELECT 
    AVG(grade) AS average_grade
FROM students;

Result:

You might have noticed that NULL is ignored again.

4. Minimum and maximum with MIN() and MAX()

Sometimes you need to find the smallest or the biggest value. That’s what MIN() and MAX() are for.

Example: Let’s find the youngest and oldest student.

SELECT 
    MIN(age) AS youngest_student, 
    MAX(age) AS oldest_student
FROM students;

Result:

We’ll talk more about these functions in the next lectures.

Getting to Know NULL

There’ll be a whole lecture about NULL soon, but in short, NULL means there’s no value. Nothing. Emptiness. A big fat zero, just as it is!

Let’s say we have a students table that stores info about students:

Name and age are always there, but grade and hobbie might be missing. If there’s no value at all in a table cell, people say it contains NULL. NULL isn’t a number or a string, it’s a special code that means there’s no value at all.