Aggregate Functions

Aggregate functions collapse many rows into a single summary value — a count, a total, an average. Used alone (without GROUP BY, covered next lesson) they summarize the entire result set into one row. SQLite's core aggregates are COUNT(), SUM(), AVG(), MIN(), MAX(), and its own GROUP_CONCAT() for joining grouped text values together. We'll run all of them against RecipeBox's recipes and reviews tables.

COUNT()

COUNT(*) counts every row. COUNT(column) counts only rows where that column is not NULL — a distinction worth remembering, since it changes the result whenever NULLs are present.

-- How many reviews exist in total?
SELECT COUNT(*) AS total_reviews FROM reviews;

-- How many reviews actually have a written comment (NULLs are skipped)?
SELECT COUNT(comment) AS reviews_with_comment FROM reviews;

-- How many distinct users have left a review?
SELECT COUNT(DISTINCT user_id) AS reviewers FROM reviews;

SUM() and AVG()

-- Total prep time across every recipe, and the average prep time
SELECT
  SUM(prep_minutes) AS total_minutes,
  AVG(prep_minutes) AS avg_minutes
FROM recipes;

-- The overall average rating across every review ever left
SELECT AVG(rating) AS site_avg_rating FROM reviews;

MIN() and MAX()

-- The quickest and slowest recipes on RecipeBox
SELECT
  MIN(prep_minutes) AS fastest_recipe,
  MAX(prep_minutes) AS slowest_recipe
FROM recipes;

-- The lowest and highest rating any single review has given
SELECT MIN(rating) AS lowest, MAX(rating) AS highest FROM reviews;

GROUP_CONCAT()

GROUP_CONCAT() is SQLite's aggregate for joining the values from a group into a single delimited string — handy for a quick summary without a separate query per row. Its syntax is GROUP_CONCAT(column, separator); the separator argument is optional, and defaults to a comma when omitted.

-- One row per recipe, listing every ingredient name comma-separated
SELECT r.title, GROUP_CONCAT(i.name) AS ingredient_list
FROM recipes r
JOIN ingredients i ON i.recipe_id = r.id
GROUP BY r.id;

-- Same idea, but with a custom separator instead of the default comma
SELECT r.title, GROUP_CONCAT(i.name, ' | ') AS ingredient_list
FROM recipes r
JOIN ingredients i ON i.recipe_id = r.id
GROUP BY r.id;

Combining Aggregates in One Query

You can compute several aggregates from the same table in a single SELECT — each one is evaluated independently over all the rows the WHERE clause lets through.

-- A one-row summary of the whole reviews table
SELECT
  COUNT(*) AS total_reviews,
  AVG(rating) AS avg_rating,
  MIN(rating) AS lowest_rating,
  MAX(rating) AS highest_rating
FROM reviews
WHERE rating IS NOT NULL;