SQLancer
Practice
←
LESSON 10

SQL (Core) - Queries with aggregates (Pt. 1)

Progress: 0%

Aggregate Functions Syntax

SELECT AGG_FUNC(column_or_expression) AS aggregate_description FROM mytable WHERE constraint_expression;

Without a specified grouping, each aggregate function runs on the entire set of result rows and returns a single aggregate summary value.

Common Aggregate Functions

Function Description
COUNT(*), COUNT(col) Counts the number of rows in the group (or non-NULL column values).
MIN(column) Finds the smallest numerical or chronological value in the group.
MAX(column) Finds the largest numerical or chronological value in the group.
AVG(column) Calculates the average numerical value across rows in the group.
SUM(column) Calculates the sum of all numerical values in the group.

Grouped Aggregates: GROUP BY Syntax

SELECT AGG_FUNC(column_or_expression) AS aggregate_description FROM mytable WHERE constraint_expression GROUP BY column;

The GROUP BY clause splits rows into distinct sub-groups based on matching column values, applying aggregate calculations to each sub-group separately.

💡

Group By Tip

When using GROUP BY, all non-aggregated columns in your SELECT clause should also be included in the GROUP BY clause.

🗄️ Schema Explorer SELECTABLE

› employees
|

🎯 Exercises Checklist:

☐ 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
⚡ Practice Console

Data Output

No results. Run query to fetch data.

Current Task Target

5 rows