Group and Aggregate Data with SQL in PostgreSQL
Aggregation in SQL is not a row-by-row calculation. Aggregate functions operate on rowsets: their role is to take many rows as input and reduce them to a single scalar value.
This rowset is defined by the FROM clause, optionally filtered by WHERE, and, when introduced, partitioned by GROUP BY.
Fundamental rule: if no GROUP BY is present, PostgreSQL treats the entire result set as a single group. This is why a query with aggregate functions and no grouping always returns exactly a single row.
Rows are selected first, filtering is applied next, and only then do aggregation functions run on the remaining set. If we think of aggregates as operating on individual lines, the rest of the course will seem confusing. If we think in terms of sets, everything aligns.
Sign in to read this course
A free account unlocks all 514 courses. 20 are readable without one.
What's inside
6 sections- 1 Table of Contents
- 2 Summarizing Data with Aggregate Functions
- 3 Grouping data with GROUP BY and HAVING
- 4 Calculations with window functions
- 5 Combining aggregation and filtering for analysis
- 6 Appendix: Course Database
More PostgreSQL courses
View all 6Join and Combine Data with SQL in PostgreSQL
Real business questions always cross these boundaries: comparing commitment by seniority, salaries by department. Relational databases are designed precisely for this.
Modify Data with SQL in PostgreSQL
The DDL includes three statements: CREATE, ALTER and DROP. These instructions form the foundation of any database creation. They define objects that store data, manage data, and host serv...
Query Data with SQL in PostgreSQL
When you want to retrieve information from a database, you query the database. These SQL statements are commonly called queries.
Query Semi-structured Data with SQL in PostgreSQL
Semi-structured data is halfway in the data universe. They are not entirely structured like a classic table, but they are not free text either. They follow a flexible structure based on t...
Write Reusable and Reliable Code with SQL in PostgreSQL
The majority of SQL problems do not stem from missing functionality. They come from logic that is copied, slightly modified, and then gradually drifts in different directions. PostgreSQL...
Interested in this course?
Contact us to book it or get a custom training plan for your team.