Summarize data with aggregates

Count rows, calculate an average and group records into meaningful summaries.

About 9 minutes · Beginner

Calculate a summary

COUNT(*) counts rows. SUM adds values and AVG computes their mean. Aggregate functions combine many rows into a smaller result.

Most aggregates ignore NULL values. COUNT(column) counts non-NULL values in that column, which can differ from COUNT(*).

SELECT COUNT(*) AS students,
       SUM(score) AS total_score,
       AVG(score) AS average_score
FROM students;

Expected output

3 | 195 | 65.0

Group related rows

GROUP BY creates one group for each distinct key. Here CASE labels each student as Pass or Retry before the rows are counted by that label.

For portable SQL, each selected expression should be an aggregate or part of the grouping. Avoid selecting an arbitrary student name next to a whole-table average.

SELECT CASE WHEN score >= 40 THEN 'Pass' ELSE 'Retry' END AS result,
       COUNT(*) AS students
FROM students
GROUP BY CASE WHEN score >= 40 THEN 'Pass' ELSE 'Retry' END
ORDER BY result;

Expected output

Pass | 2
Retry | 1

Filter before or after grouping

WHERE filters individual rows before grouping. HAVING filters the resulting groups, for example HAVING COUNT(*) >= 2.

Choose the stage based on the question: “students scoring at least 40” is a row filter; “groups containing at least two students” is a group filter.

Try it yourself

Count the students who scored at least 40.

Show a possible solution

SELECT COUNT(*) AS passed FROM students WHERE score >= 40;

Keep exploring: official SQL documentation.

0 of 5 completed on this device