Zerethon IDE
Sign in

COUNT, SUM, AVG and GROUP BY

Summarise rows with aggregate functions, one row per group with GROUP BY, and filter groups with HAVING.

Runs in Zerethon IDE · SQLite 3 Official SQL documentation ↗
main.sql 
SELECT c.title,
       count(e.student_id) AS students,
       round(avg(e.grade), 2) AS avg_grade,
       max(e.grade) AS best
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.id
GROUP BY c.id
HAVING count(e.student_id) >= 1
ORDER BY students DESC, c.title;
setup/schema.sql 
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, year INTEGER);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students (id),
    course_id INTEGER REFERENCES courses (id),
    grade REAL
);

INSERT INTO students VALUES (1, 'Ada', 2), (2, 'Alan', 3), (3, 'Grace', 2), (4, 'Linus', 1);
INSERT INTO courses VALUES (1, 'Algorithms'), (2, 'Databases'), (3, 'Networks');
INSERT INTO enrollments VALUES (1, 1, 9.5), (1, 2, 8.0), (2, 1, 7.5), (3, 2, 9.0), (3, 3, NULL);

Result

titlestudentsavg_gradebest
Algorithms28.59.5
Databases28.59
Networks1NULLNULL
Uses the school database in setup/schema.sql (shown below the query): students, courses and enrollments.
  • count(*) counts rows; count(column) counts rows where that column is not NULL.
  • avg, sum, min, max ignore NULLs — Networks has one student but no grade, so its average is NULL.
  • WHERE filters rows before grouping, HAVING filters groups after.
  • Every selected column should be grouped or inside an aggregate.

Related topics

Official references