SQL Aggregations, GROUP BY, HAVING & Subqueries

SQL Aggregations, GROUP BY, HAVING & Subqueries

Analytics dashboards, progress trackers, and financial reports rarely display raw individual rows—they summarize thousands of rows into counts, averages, totals, and top performers per category. SQL accomplishes this directly inside the database engine via aggregate functions paired with GROUP BY and HAVING.

1. Core SQL Aggregate Functions

2. Summarizing Buckets with GROUP BY

When combining non-aggregated columns with aggregate functions, every non-aggregated column in the SELECT list must appear in the GROUP BY clause:

SELECT
    c.title AS course_title,
    COUNT(l.id) AS lesson_count,
    COALESCE(SUM(l.views), 0) AS total_views
FROM courses c
LEFT JOIN lessons l ON l.course_id = c.id AND l.is_published = 1
WHERE c.is_published = 1
GROUP BY c.id, c.title
ORDER BY lesson_count DESC;

3. WHERE vs. HAVING: Know When Each Runs

Beginners frequently confuse WHERE and HAVING because both filter data. The difference hinges on timing:

• WHERE filters individual rows before grouping—use it to restrict dates, published statuses, or categories first so the engine aggregates fewer rows.
• HAVING filters collapsed groups after GROUP BY—making it the only place where aggregate expressions like HAVING COUNT(l.id) >= 5 are legal.

4. Distinct Counts & Conditional Aggregation with CASE WHEN

Often one dashboard query needs multiple metrics side by side—for instance counting both total lessons and published-only lessons per course without running separate queries:

SELECT
    c.title,
    COUNT(l.id) AS total_lessons,
    SUM(CASE WHEN l.is_published = 1 THEN 1 ELSE 0 END) AS published_lessons,
    COUNT(DISTINCT l.language) AS languages_used
FROM courses c
LEFT JOIN lessons l ON l.course_id = c.id
GROUP BY c.id, c.title;

Remember SQL's strict ONLY_FULL_GROUP_BY behavior in modern MySQL 8 and PostgreSQL: never select an unaggregated non-grouped column unless it is functionally dependent on the primary key included in GROUP BY.

5. Subqueries and Common Table Expressions (WITH / CTEs)

Sometimes you need a two-step calculation—for example, finding every course whose views exceed the platform average. Instead of hardcoding numbers, embed a scalar subquery or use a readable CTE (Common Table Expression):

WITH course_totals AS (
    SELECT course_id, COUNT(*) AS total_lessons
    FROM lessons
    WHERE is_published = 1
    GROUP BY course_id
)
SELECT c.title, ct.total_lessons
FROM courses c
JOIN course_totals ct ON c.id = ct.course_id
WHERE ct.total_lessons >= 5;

6. Hands-On Analytics Query Exercise & Key Takeaways

Open the live SQLite runner snippet below, add two more rows to the sales table (including a Tools sale of 450), adjust the HAVING threshold, and test ordering results by avg_order DESC.

• Let the database summarize rows in one pass: Computing counts, totals, averages, and conditional buckets inside SQL avoids transferring thousands of unneeded detail rows across the network into application memory.
• Filter before grouping whenever possible: Narrowing rows with WHERE prior to GROUP BY shrinks summary work dramatically, leaving HAVING solely for conditions referencing aggregate results such as HAVING COUNT(*) >= 5.
• Watch out for NULL skew in averages: Because AVG(score) skips NULL entries instead of counting them as zero, wrap optional numeric columns with COALESCE(score, 0) whenever unanswered records should count against an average.
• Break multi-stage logic into CTEs: Naming intermediate aggregate tables with WITH ... AS (...) turns deeply nested subqueries into clean, self-documenting top-to-bottom analytics pipelines.