SQL GROUP BY and HAVING: Aggregation Done Right
GROUP BY aggregates rows, HAVING filters the groups. The distinction took me a while to internalize. Here is how I use both. GROUP BY and HAVING are the pair of clauses that turn raw rows into aggregated summaries. GROUP BY groups rows that share values in specified columns, and aggregate functions like COUNT and SUM compute a single value per group. HAVING filters the groups, the way WHERE filters the rows before grouping. The distinction between WHERE and HAVING took me a while to internalize, and mixing them up is a common source of wrong results. Here is how I use both correctly. The Basic GROUP BY GROUP BY takes one or more columns. All rows with the same values in those columns form a group. The SELECT list can include the grouping columns and aggregate functions over the other columns. Each row in the output represents one group. SELECT category, COUNT(*) AS product_count, AVG(price) AS avg_price FROM products GROUP BY category; This returns one row per category with the count of products and the average price. The grouping column appears in the SELECT list, and the aggregate functions summarize the rest.…