Summarizing Data Using GROUP BY and Aggregate Functions

So far, we’ve learned how to read data with SELECT, filter rows with WHERE, and sort results with ORDER BY. But what if you don’t just want to see every row — what if you want to summarize data? That’s where GROUP BY and aggregate functions come in.

Basic Aggregate Function Syntax

SELECT column1,
	   AGGREGATE_FUNCTION(column2)
FROM schema_name.table_name
GROUP BY column1;

SELECT column1,
	   AGGREGATE_FUNCTION(column2)
FROM schema_name.table_name
GROUP BY column1;

• GROUP BY takes rows that share the same value in one or more columns and groups them together.
• Aggregate functions like COUNT, SUM, or AVG are then applied to each group.

Universal Aggregate Functions

The functions listed below are supported by all major relational databases. Some platforms also provide additional aggregate functions (like STRING_AGG in PostgreSQL or GROUP_CONCAT in MySQL), but the core aggregation functions shown here will work everywhere.

• COUNT() → number of rows
  • COUNT(*) or COUNT(1) counts all rows
  • COUNT(column_name) counts only non-NULL values
• SUM() → total of numeric values
  • Must be applied to a numeric column type
  • Ignores NULL values
• AVG() → average of numeric values
  • Must be applied to a numeric column type
  • Ignores NULL values, which may skew results if not handled properly
• MIN() → smallest value
  • Can be applied to numeric, text, or date columns
• MAX() → largest value
  • Can be applied to numeric, text, or date columns

It doesn’t make much sense to group on unique columns, that will return a single record for each row. For these examples we’ve expanded the paintings table used in previous posts to properly demonstrate aggregate functions.

Table: Gallery.Paintings

Example: Count paintings per country

SELECT CountryOfOrigin,
	   COUNT(1) AS PaintingCount
FROM Gallery.Paintings
GROUP BY CountryOfOrigin
ORDER BY PaintingCount DESC,
		 CountryOfOrigin ASC;

SELECT CountryOfOrigin,
	   COUNT(1) AS PaintingCount
FROM Gallery.Paintings
GROUP BY CountryOfOrigin
ORDER BY PaintingCount DESC,
		 CountryOfOrigin ASC;

Result:

Making Results Easier to Read with Aliases

Notice the AS PaintingCount in the example above? The AS keyword is used to define a column alias.

An alias is a temporary name you give to a column or calculation in your query results. It doesn’t change the underlying table—it just assigns the column a clear, descriptive identifier, making your output easier to read.

Without an alias, the column name would appear as a generic name determined by the database platform such as (No column name), which isn’t very user-friendly.

💡Tip: In most databases, AS is optional. You could simply write COUNT(*) OrderCount and get the same result. However, using AS is considered best practice because it makes your queries easier for someone else to follow.

Aliases with Spaces

Best practice is to keep aliases simple, without spaces. However, sometimes reporting requirements ask for more “friendly” names, like Painting Count instead of PaintingCount.

You can do this by enclosing the alias in either double quotes (" ") or square brackets ([ ]), depending on your database platform.

• SQL Server → square brackets [ ] or double quotes " "

SELECT CountryOfOrigin,
	   COUNT(1) AS [Painting Count]
FROM Gallery.Paintings
GROUP BY CountryOfOrigin;

SELECT CountryOfOrigin,
	   COUNT(1) AS [Painting Count]
FROM Gallery.Paintings
GROUP BY CountryOfOrigin;

• Oracle / PostgreSQL / MySQL / SQLite → double quotes " "

SELECT CountryOfOrigin,
	   COUNT(1) AS "Painting Count"
FROM Gallery.Paintings
GROUP BY CountryOfOrigin;

SELECT CountryOfOrigin,
	   COUNT(1) AS "Painting Count"
FROM Gallery.Paintings
GROUP BY CountryOfOrigin;

Result:

📌 Important Note: Using spaces can make queries harder to maintain, because you’ll need to quote the alias again if you reference it in an outer query. Best practice is to stick with simple names unless there’s a strong reporting need.

What About Single Quotes?

Single quotes (' ') are not valid for identifiers like aliases. In SQL, single quotes always represent string literals, not names.

For example, if you write:

SELECT CountryOfOrigin,
	   COUNT(1) AS 'Painting Count'
FROM Gallery.Paintings
GROUP BY CountryOfOrigin;

SELECT CountryOfOrigin,
	   COUNT(1) AS 'Painting Count'
FROM Gallery.Paintings
GROUP BY CountryOfOrigin;

Some tools will still display the output column heading as Painting Count, but what’s really happening is the database is treating 'Painting Count' as a literal string. It looks like it worked, but technically you just selected a constant string and renamed the count column with no real alias.

This can cause errors in more complex queries or when porting code between platforms, and should be avoided.

Using Multiple Aggregations

You can use more than one aggregation at a time, and aliases make the output much easier to understand.

Example: Works per artist with earliest and latest year

SELECT Artist,
	   COUNT(1) AS PaintingCount,
	   MIN(YearCreated) AS EarliestWork,
	   MAX(YearCreated) AS LatestWork
FROM Gallery.Paintings
GROUP BY Artist
ORDER BY PaintingCount DESC,
		 Artist ASC;

SELECT Artist,
	   COUNT(1) AS PaintingCount,
	   MIN(YearCreated) AS EarliestWork,
	   MAX(YearCreated) AS LatestWork
FROM Gallery.Paintings
GROUP BY Artist
ORDER BY PaintingCount DESC,
		 Artist ASC;

Result:

Adding More Columns

Sometimes you’ll want to group multiple columns to provide more context in results.

Keep in mind, if a column appears in the SELECT list and isn’t aggregated, it must be listed in GROUP BY. This ensures that the database knows exactly how to group rows before calculating the summary values.

Example: Paintings by country and artist

SELECT CountryOfOrigin,
	   Artist,
	   COUNT(1) AS PaintingCount
FROM Gallery.Paintings
GROUP BY CountryOfOrigin,
		 Artist
ORDER BY CountryOfOrigin ASC,
		 PaintingCount DESC,
		 Artist ASC;

SELECT CountryOfOrigin,
	   Artist,
	   COUNT(1) AS PaintingCount
FROM Gallery.Paintings
GROUP BY CountryOfOrigin,
		 Artist
ORDER BY CountryOfOrigin ASC,
		 PaintingCount DESC,
		 Artist ASC;

Result:

Filtering Groups with HAVING

You already know WHERE filters rows before grouping. But what if you want to filter after the grouping? That’s where HAVING comes in.

• WHERE filters rows before grouping.
• HAVING filters groups after aggregation.

Example: Only countries with more than 3 paintings

SELECT CountryOfOrigin,
	   COUNT(1) AS PaintingCount
FROM Gallery.Paintings
GROUP BY CountryOfOrigin
HAVING COUNT(1) > 3
ORDER BY PaintingCount DESC,
		 CountryOfOrigin;

SELECT CountryOfOrigin,
	   COUNT(1) AS PaintingCount
FROM Gallery.Paintings
GROUP BY CountryOfOrigin
HAVING COUNT(1) > 3
ORDER BY PaintingCount DESC,
		 CountryOfOrigin;

Result:

Key Takeaways

• Use aggregate functions (COUNT, SUM, AVG, MIN, MAX) to summarize data.
• GROUP BY forms groups; aggregates compute summaries per group.
• Every selected column must be aggregated or listed in GROUP BY.
• Aliases (AS) make results readable and maintainable.
• WHERE filters rows before grouping; HAVING filters groups after aggregation.

Once you’re comfortable grouping rows by columns, you’ll be ready to explore more powerful options. In the next post, we’ll look at more ways to group data by building multiple levels of summaries in a single query.