In addition to the simple expressions that we introduced last lesson, SQL also supports the use of aggregate expressions (or functions) that allow you to summarize information about a group of rows of data. With the Pixar database that you've been using, aggregate functions can be used to answer questions like, "How many movies has Pixar produced?", or "What is the highest grossing Pixar film each year?".

Select query with aggregate functions over all rows

**SELECT AGG_FUNC(_column_or_expression_) AS aggregate_description**, … FROM mytable WHERE _constraint_expression_;

Without a specified grouping, each aggregate function is going to run on the whole set of result rows and return a single value. And like normal expressions, giving your aggregate functions an alias ensures that the results will be easier to read and process.

Common aggregate functions

Here are some common aggregate functions that we are going to use in our examples:

Grouped aggregate functions

In addition to aggregating across all the rows, you can instead apply the aggregate functions to individual groups of data within that group (ie. box office sales for Comedies vs Action movies). This would then create as many results as there are unique groups defined as by the GROUP BY clause.

Select query with aggregate functions over groups

SELECT AGG_FUNC(_column_or_expression_) AS aggregate_description, … FROM mytable WHERE _constraint_expression_ **GROUP BY column**;

The GROUP BY clause works by grouping rows that have the same value in the column specified.

Exercise

For this exercise, we are going to work with our Employees table. Notice how the rows in this table have shared data, which will give us an opportunity to use aggregate functions to summarize some high-level metrics about the teams. Go ahead and give it a shot.

1. Find the longest time that an employee has been at the studio
	1. SELECT SUM(years_employed) FROM employees GROUP BY name ORDER BY 1 DESC LIMIT 1;
	2. SELECT MAX(total_years) FROM (SELECT SUM(years_employed) AS total_years FROM employees GROUP BY name) AS sub;
2. For each role, find the average number of years employed by employees in that role
	1. SELECT Role, AVG(Years_employed) FROM employees GROUP BY Role;
3. Find the total number of employee years worked in each building
	1. SELECT Building, Sum(Years_employed) FROM employees GROUP BY Building;

Exercise 10 — Tasks

1. Find the longest time that an employee has been at the studio ✓
2. For each role, find the average number of years employed by employees in that role ✓
3. Find the total number of employee years worked in each building ✓

This page is SQLBolt Lesson 10: "Queries with aggregates (Pt. 1)." It explains how to use SQL aggregate functions to summarize data in a table. The lesson covers:

• Aggregate functions like COUNT, MIN, MAX, AVG, and SUM, which are used to summarize numeric data across rows.
• Using aggregate functions on whole tables (e.g., finding total movies produced, highest-grossing film).
• GROUP BY clause to get aggregate summaries per group (e.g., sales by genre).
• A sample Employees table is provided for practice.

The exercises ask you to:

• Find the longest time an employee has been at the studio.
• For each role, find the average years employed.
• Find the total employee years worked in each building.

The content is interactive and designed to help learners practice SQL aggregate queries directly on example data.[1]

1

Here are alternative ways to write the SQL query:

Original: ✅

SELECT SUM(years_employed) FROM employees GROUP BY name ORDER BY 1 DESC LIMIT 1;

Other equivalent versions:

• Using a subquery to select the maximum value: ✅

  SELECT MAX(total_years) 
  FROM (SELECT SUM(years_employed) AS total_years FROM employees GROUP BY name) AS sub;
  

• Using ORDER BY the alias (if your SQL dialect allows aliases in ORDER BY): ✅

  SELECT SUM(years_employed) AS total_years 
  FROM employees 
  GROUP BY name 
  ORDER BY total_years DESC 
  LIMIT 1;
  

• Using window functions (if supported):

  SELECT MAX(sum_years) 
  FROM (
      SELECT SUM(years_employed) OVER (PARTITION BY name) AS sum_years 
      FROM employees
  ) AS t;
  

• Using DISTINCT in a windowed result to only get unique totals (useful if names might repeat):

  SELECT DISTINCT SUM(years_employed) OVER (PARTITION BY name) AS total_years
  FROM employees
  ORDER BY total_years DESC
  LIMIT 1;
  

All of these achieve the goal of finding the longest time that an employee has been at the studio (i.e., the maximum total years employed by any single person). The subquery and window function versions are especially useful in more complex queries or different SQL dialects.

1

Here are a few alternative ways to write the following SQL query:

Original:

SELECT Role, AVG(Years_employed) FROM employees GROUP BY Role;

Other valid versions:

• Using an alias for readability: ✅

  SELECT Role, AVG(Years_employed) AS avg_years FROM employees GROUP BY Role;
  

• If your SQL environment supports window functions, you could get the average years employed for each role on every row:

  SELECT Role, AVG(Years_employed) OVER (PARTITION BY Role) AS avg_years FROM employees;
  

  Note: This gives repeated results for each employee, not grouped — best when you want detail per row.

• Using a subquery (not generally necessary, but another style): ✅

  SELECT Role, avg_years 
  FROM (
    SELECT Role, AVG(Years_employed) AS avg_years
    FROM employees
    GROUP BY Role
  ) sub;
  

• Use CAST for a specific decimal format (if you want, for example, 2 decimals): ✅

  SELECT Role, CAST(AVG(Years_employed) AS DECIMAL(5,2)) AS avg_years
  FROM employees
  GROUP BY Role;
  

All of these alternatives give you the average years employed by employees in each role. The choice depends on your SQL dialect and whether you want formatting or extra detail.

1

Here are some alternative ways to write your SQL query:

Original:

SELECT Building, SUM(Years_employed) FROM employees GROUP BY Building;

Other valid versions:

• Using an alias for the SUM result: ✅

  SELECT Building, SUM(Years_employed) AS total_years FROM employees GROUP BY Building;
  

• With a subquery (not required here, but possible): ✅

  SELECT Building, total_years
  FROM (
    SELECT Building, SUM(Years_employed) AS total_years
    FROM employees
    GROUP BY Building
  ) AS sub;
  

• If you want the results ordered by total years: ✅

  SELECT Building, SUM(Years_employed) AS total_years
  FROM employees
  GROUP BY Building
  ORDER BY total_years DESC;
  

• If your SQL dialect supports window functions and you want the sum repeated for each building’s employees:

  SELECT Building, SUM(Years_employed) OVER (PARTITION BY Building) AS total_years
  FROM employees;
  

  Note: This style is best if you also want to see individual employee details alongside the total per building.

All of these versions calculate the total number of employee years worked in each building. The alias and ordering can help with readability and sorting.

1