Aggregation

Summarizing Data

Aggregate functions summarize data across multiple rows. Instead of seeing individual records, you get counts, totals, averages, and more. Combined with GROUP BY, they let you analyze data at any level of granularity.

Aggregate Functions

  SELECT COUNT(*) FROM Students;              -- Total students
  SELECT COUNT(DISTINCT DeptID) FROM Students; -- Number of departments
  SELECT AVG(Age) FROM Students;              -- Average age
  SELECT SUM(Credits) FROM Courses;           -- Total credits
  SELECT MIN(Age) FROM Students;              -- Youngest
  SELECT MAX(Age) FROM Students;              -- Oldest

GROUP BY

GROUP BY groups rows with the same values in specified columns, allowing aggregates to be computed per group:

  -- Count students per department
  SELECT DeptID, COUNT(*) AS StudentCount
  FROM Students
  GROUP BY DeptID;

  Result:
  ┌───────┬──────────────┐
  │DeptID │ StudentCount │
  ├───────┼──────────────┤
  │ 10    │ 25           │
  │ 20    │ 18           │
  │ 30    │ 32           │
  └───────┴──────────────┘

HAVING Clause

HAVING filters groups (like WHERE filters rows):

  -- Departments with more than 20 students
  SELECT DeptID, COUNT(*) AS StudentCount
  FROM Students
  GROUP BY DeptID
  HAVING COUNT(*) > 20;

  -- Average grade per course, only showing courses with avg > 3.5
  SELECT CourseID, AVG(GradeNum) AS AvgGrade
  FROM Grades
  GROUP BY CourseID
  HAVING AVG(GradeNum) > 3.5;

Complete Query Structure

  SELECT    columns
  FROM      table
  WHERE     row conditions
  GROUP BY  columns
  HAVING    group conditions
  ORDER BY  columns
  LIMIT     n;

  Execution order:
  FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT