Aggregate functions and GROUP BY

-- Orders(OrderID, CustomerID, Amount, Region, OrderDate, Status)

-- Basic aggregation
SELECT
    COUNT(*)              AS TotalOrders,
    COUNT(DISTINCT CustomerID) AS UniqueCustomers,
    SUM(Amount)           AS TotalRevenue,
    AVG(Amount)           AS AvgOrderValue,
    MIN(Amount)           AS SmallestOrder,
    MAX(Amount)           AS LargestOrder
FROM Orders WHERE Status = 'Completed';

-- GROUP BY with HAVING
SELECT
    Region,
    EXTRACT(YEAR FROM OrderDate) AS Year,
    COUNT(*)                     AS OrderCount,
    SUM(Amount)                  AS Revenue
FROM Orders WHERE Status = 'Completed'
GROUP BY Region, EXTRACT(YEAR FROM OrderDate)
HAVING SUM(Amount) > 100000
ORDER BY Revenue DESC;

-- ROLLUP: adds subtotals + grand total
SELECT
    COALESCE(Region, 'ALL')  AS Region,
    COALESCE(Status, 'ALL')  AS Status,
    SUM(Amount) AS Revenue
FROM Orders
GROUP BY ROLLUP(Region, Status);

-- Conditional aggregation (CASE inside SUM)
SELECT
    SUM(CASE WHEN Status = 'Completed' THEN Amount ELSE 0 END) AS CompletedRev,
    SUM(CASE WHEN Status = 'Cancelled' THEN 1     ELSE 0 END) AS CancelCount
FROM Orders;

Two rules explain nearly every GROUP BY error message you will ever see. Rule 1: every column in the SELECT list must be either inside an aggregate function or listed in GROUP BY. Otherwise the database cannot know which row's value to show for a collapsed group. Rule 2: WHERE filters rows before grouping; HAVING filters groups after aggregating. That ordering is why WHERE SUM(Amount) > 100 is always an error — at WHERE time, no sums exist yet.

The COUNT trap that breaks reports: COUNT(*) counts rows. COUNT(column) counts non-NULL values in that column. COUNT(DISTINCT column) counts distinct non-NULL values. So on a table of 100 rows where 30 have NULL manager_id: COUNT(*) = 100 but COUNT(manager_id) = 70. The same NULL rule silently affects AVG — AVG(score) divides by the count of non-NULL scores, not by the row count, so NULLs are ignored rather than treated as zero. If you want NULLs counted as zero, write AVG(COALESCE(score, 0)). Also: SUM of an empty set returns NULL, not 0 — wrap it in COALESCE when a report must show 0.

Window functions — analytics without losing rows

-- Ranking
SELECT Name, Salary, DeptID,
    ROW_NUMBER() OVER (ORDER BY Salary DESC)                     AS GlobalRank,
    RANK()       OVER (ORDER BY Salary DESC)                     AS RankWithGaps,
    DENSE_RANK() OVER (ORDER BY Salary DESC)                     AS RankNoGaps,
    RANK()       OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS DeptRank,
    NTILE(4)     OVER (ORDER BY Salary DESC)                     AS SalaryQuartile
FROM Employee;
-- ROW_NUMBER: 1,2,3,4,5     (no ties)
-- RANK:       1,2,2,4,5     (tie gets same rank, skips next)
-- DENSE_RANK: 1,2,2,3,4     (tie gets same rank, no skip)

-- Running total and 7-day moving average
SELECT OrderDate, Amount,
    SUM(Amount) OVER (ORDER BY OrderDate
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal,
    AVG(Amount) OVER (ORDER BY OrderDate
                      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)         AS MovingAvg7Day
FROM Orders WHERE CustomerID = 101;

-- LAG/LEAD: access previous/next row
SELECT OrderDate, Amount,
    LAG(Amount)  OVER (ORDER BY OrderDate) AS PrevAmount,
    LEAD(Amount) OVER (ORDER BY OrderDate) AS NextAmount,
    Amount - LAG(Amount) OVER (ORDER BY OrderDate) AS ChangeFromPrev
FROM Orders WHERE CustomerID = 101;

The subtlety that trips up almost everyone: the frame clause. Writing OVER (ORDER BY col) without an explicit frame silently applies the SQL default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — and RANGE treats ties as a single unit. If three orders share the same date, a running total using RANGE jumps by all three at once, giving each of them the same total. ROWS counts physical rows instead and increments one at a time. For running totals you almost always want ROWS:

Window function rules worth memorizing: Window functions execute after WHERE, GROUP BY, and HAVING — which is why you cannot filter on a window result in WHERE (WHERE ROW_NUMBER() OVER (...) = 1 is an error). Wrap it in a CTE or subquery and filter in the outer query; that pattern is exactly how top-N-per-group is written. Also: window functions can be used with GROUP BY, but they then operate on the grouped rows, not the original ones — SUM(SUM(Amount)) OVER (ORDER BY Region) is legal and computes a running total of per-region sums.

SQL integrity constraints

CREATE TABLE Product (
    ProductID    SERIAL         PRIMARY KEY,        -- auto-increment + NOT NULL + UNIQUE
    SKU          VARCHAR(20)    NOT NULL UNIQUE,    -- alternate key
    Name         VARCHAR(200)   NOT NULL,
    Price        DECIMAL(10,2)  NOT NULL CHECK (Price >= 0),
    DiscountPct  DECIMAL(5,2)   DEFAULT 0 CHECK (DiscountPct BETWEEN 0 AND 100),
    CategoryID   INT            NOT NULL
                                REFERENCES Category(CategoryID)
                                ON DELETE RESTRICT ON UPDATE CASCADE,
    Stock        INT            NOT NULL DEFAULT 0 CHECK (Stock >= 0),
    -- Table-level constraint across multiple columns
    CONSTRAINT chk_min_sale_price CHECK (Price * (1 - DiscountPct/100) >= Price * 0.1)
);

Practice questions

1. Difference between RANK() and DENSE_RANK()? (Answer: Both give same rank to ties. RANK() skips next ranks after tie (1,2,2,4). DENSE_RANK() does not skip (1,2,2,3). Use DENSE_RANK for top-N filtering.)
2. SELECT DeptID, AVG(Salary) FROM Employee HAVING AVG(Salary) > 50000 — is this valid? (Answer: Yes — without GROUP BY, entire table is one group. Returns one row if company average exceeds 50000.)
3. COUNT(*) vs COUNT(column_name)? (Answer: COUNT(*) counts all rows including NULLs. COUNT(column) counts only non-NULL values in that column.)
4. What does PARTITION BY do in a window function? (Answer: Resets the window computation for each partition group — like GROUP BY but without collapsing rows. Each partition gets its own independent ranking/aggregation.)
5. What SQL constraint prevents EndDate before StartDate? (Answer: CHECK (EndDate >= StartDate) — user-defined integrity constraint.)
6. Why does WHERE SUM(Amount) > 1000 throw an error, and what is the fix? (Answer: WHERE executes before grouping and aggregation, so no SUM exists yet. Aggregate filters belong in HAVING, which runs after GROUP BY: ... GROUP BY CustomerID HAVING SUM(Amount) > 1000. Rule: WHERE filters rows, HAVING filters groups.)
7. A table has 100 rows; 30 have NULL in bonus. What do COUNT(*), COUNT(bonus), and AVG(bonus) return? (Answer: COUNT(*) = 100 (counts rows). COUNT(bonus) = 70 (counts non-NULL values). AVG(bonus) = SUM(bonus)/70 — it divides by non-NULL count, silently ignoring the 30 NULLs rather than treating them as zero. Use AVG(COALESCE(bonus, 0)) to divide by 100.)
8. Running total with SUM(Amount) OVER (ORDER BY OrderDate) gives identical totals for orders on the same date. Why? (Answer: The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE treats tied ORDER BY values as one peer group — all same-date rows get the same cumulative sum. Fix: specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to count physical rows one at a time.)
9. Why is WHERE ROW_NUMBER() OVER (ORDER BY Salary DESC) <= 3 invalid, and how do you write top-3-per-department? (Answer: Window functions are evaluated after WHERE, so the value does not exist at filter time. Compute it in a CTE or subquery, then filter outside: WITH r AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY DeptID ORDER BY Salary DESC) rn FROM Employee) SELECT * FROM r WHERE rn <= 3.)