SQLMarrow / Course Academy Experience

SQL & Databases Masterclass Course

Welcome to the ultimate postgresql tutorial for beginners 2026. Work through interactive sql practice problems with solutions, master our comprehensive sql window functions tutorial modules, and prepare for tough sql interview questions at Google and Amazon entirely in your browser. Claim your course completion XP and build production-ready database mastery today!

COURSE PROGRESS
0%
0 of 40 Modules Solved
COURSE SYLLABUS
40 Lessons
LESSON WORKSPACEIntermediate

SQL Lesson 11: Queries with aggregates (Pt. 2)

Introduction & Core Concept

Global aggregations tell you the overall total, but business strategy requires segmentation: "What is our average revenue *by department*?" or "What is our total inventory *by product category*?"

To compute metrics across discrete categories, we use the GROUP BY clause. This splits your table into independent buckets based on the grouping column and runs your aggregate calculations independently inside each bucket.

SELECT category, AVG(price) FROM products GROUP BY category;
The HAVING Clause (Filtering Aggregates)

Suppose you want to filter your grouped results to show *only categories whose average price exceeds $100*. You cannot use the standard WHERE clause because WHERE filters raw individual rows before aggregation occurs.

To filter aggregated group summaries, you must use the HAVING clause.

SELECT category, AVG(price) AS avg_price 
FROM products 
GROUP BY category 
HAVING avg_price > 100.00;
Why & Where We Use It
  • Why We Use It: Unlocks multi-dimensional reporting. GROUP BY and HAVING allow analysts to isolate high-performing cohorts, detect underperforming branches, and segment customer spending tiers.
  • Where We Use It: Monthly sales reports by region, isolating high-risk banking segments, and analyzing website traffic by referral channel.
    • Real-World Example

    ApexBank wants a summary report of total cash reserves grouped by account type (Checking vs. Savings). However, the auditing team wants to filter out small boutique segments, displaying only account types that hold collective assets greater than $10,000.

    Best Practices: What to Do & What NOT to Do
  • What to Do: Every standalone column listed in your SELECT statement must be explicitly included in your GROUP BY clause.
  • What NOT to Do: Never attempt to use WHERE to filter aggregate function outputs (e.g., WHERE SUM(amount) > 1000). Always use HAVING for aggregate constraints!
    • Syntax & Pro Tips
    SELECT account_type, SUM(balance) AS total_balance 
FROM bank_accounts 
GROUP BY account_type 
HAVING total_balance > 10000.00;
    Interactive Sandboxed Terminal (Preloaded DB Schema: APEXBANK)
    SQL Query WorkspaceSQLite v3.45 (WASM Mode)
    QUICK INSERT:
    1