Mastering SQL Aggregations and Having Clauses Like a Pro

Data analysis relies heavily on summarizing datasets. SQL aggregation queries let you consolidate thousands of records into meaningful metrics. Grasping the logical order of query execution (FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY) is essential.

The Difference Between WHERE and HAVING

One of the most frequent interview traps is using the HAVING clause when WHERE is appropriate, or vice versa:

- WHERE filters rows before aggregation occurs. It cannot reference aggregate functions like SUM() or AVG(). - HAVING filters the aggregated groups after GROUP BY evaluates. It operates strictly on group summary values.

-- Retrieve departments with average salary > 100k
SELECT DeptID, AVG(Salary) as AvgSalary
FROM Employee
WHERE JobTitle != 'CEO' -- Filter individual rows
GROUP BY DeptID
HAVING AVG(Salary) > 100000; -- Filter grouped rows

COUNT(*) vs COUNT(column): A Critical Distinction

SELECT
  COUNT(*) as TotalRows,         -- counts all rows including NULLs
  COUNT(ManagerID) as HasManager, -- counts only non-NULL ManagerID rows
  COUNT(DISTINCT DeptID) as Depts -- counts distinct non-NULL values
FROM Employee;

COUNT(*) counts every row in the group, including rows where all columns are NULL. COUNT(column) counts only rows where that specific column is not NULL. COUNT(DISTINCT column) counts only unique non-NULL values. This three-way distinction is a classic interview question that catches candidates who assume COUNT always behaves the same way.

Filtering Groups: The HAVING Clause in Depth

-- Departments with more than 5 employees AND average salary above 80k
SELECT DeptID,
       COUNT(*) as HeadCount,
       AVG(Salary) as AvgSalary
FROM Employee
GROUP BY DeptID
HAVING COUNT(*) > 5
   AND AVG(Salary) > 80000
ORDER BY AvgSalary DESC;

HAVING can reference any aggregate function. You can also reference the column you grouped by (DeptID), but you cannot reference columns that are not in the GROUP BY and not wrapped in an aggregate. Notice that the AVG(Salary) computed in SELECT is not reused by HAVING - HAVING computes its own aggregate independently (though most optimizers will share the computation).

GROUP BY with Multiple Columns

-- Revenue per region per product category
SELECT Region, Category,
       SUM(Revenue) as TotalRevenue,
       COUNT(DISTINCT OrderID) as OrderCount
FROM Sales
GROUP BY Region, Category
ORDER BY Region, TotalRevenue DESC;

When grouping by multiple columns, every unique combination of the grouped columns forms a separate group. (North, Electronics) is a different group from (South, Electronics) and from (North, Furniture). This is the foundation of pivot-table-style analytical reporting and is used constantly in business intelligence queries.

ROLLUP and CUBE: Advanced Grouping Extensions

ROLLUP(A, B) produces subtotals: all combinations of (A, B), then subtotals for (A alone), then a grand total. CUBE(A, B) produces subtotals for all possible subset combinations: (A, B), (A alone), (B alone), and grand total. These extensions are used in reporting queries where you need hierarchical subtotals. GROUPING_ID() lets you distinguish which rows are subtotals vs. detail rows.