LECTURES / MODULE 2
Summarize and compare
Move from individual records to totals, groups, and comparisons with a defensible denominator.
Detailed lessons in this module
Open the full explanations, examples, and SQL exercises directly.
1 / 4
Count rows and known values
COUNT(*) counts rows. COUNT(calories) counts rows with a non-NULL calorie value. COUNT(DISTINCT category) counts different known categories. Always say what is being counted.
NULL means a value is missing or unknown; it is not automatically zero. AVG ignores NULL values, so an average may describe only part of the menu. State the denominator alongside your conclusion.
SELECT COUNT(*) AS items, COUNT(calories) AS known_calories FROM menu_items;
Both aggregates consume the same menu_items rows, but COUNT(calories) ignores rows where that value is NULL. Subtracting known_calories from items reveals how many rows lack that field.
This returns one summary row because there is no GROUP BY. Neither number measures the number of categories; that requires counting distinct category values.
Explore this concept →Explain it in your own words
Why are item_count and category_count answering different questions?
2 / 4
Transform values without losing their meaning
Expressions operate on values. ROUND controls displayed precision; it does not make an estimate more accurate. UPPER changes presentation; it does not repair inconsistent underlying categories.
COALESCE chooses the first non-NULL argument. Replacing an unknown calorie value with zero would create a new factual claim. Use an explicit missing-data label in reports or show the missing count separately.
SELECT name, ROUND(price * 1.10, 2) AS proposed_price FROM menu_items ORDER BY id LIMIT 5;
Multiplication applies the proposed increase to each item. ROUND(..., 2) formats the calculated number to two decimal places for comparison.
The original price is unchanged. A real pricing decision would also need a rounding policy, communication plan, and an update statement with a deliberate scope.
Explore this concept →Explain it in your own words
Does this query change the menu prices? What would you verify before applying an actual update?
3 / 4
Choose the unit of your summary
GROUP BY creates one result group for each distinct grouping value. Grouping by category answers a category-level question; grouping by category and size creates a finer comparison.
Every selected non-aggregate value should describe the group unambiguously. SQLite can accept a bare column outside GROUP BY, but acceptance does not make an arbitrary item name a meaningful label for a category average.
SELECT category, COUNT(*) AS items, ROUND(AVG(price), 2) AS average_price FROM menu_items GROUP BY category ORDER BY category;
Each category becomes one group. COUNT counts its menu rows and AVG summarizes their prices; the result is ordered by category so teams can compare it consistently.
The average is per menu item in this fixture, not weighted by purchases. You cannot describe it as the average price customers paid without appropriate order-item data.
Explore this concept →Explain it in your own words
If you add size to GROUP BY, why might the number of result rows increase?
4 / 4
Filter rows before groups, groups after aggregation
WHERE removes input rows before aggregation. HAVING removes groups after aggregation. An average over all menu items is different from an average over only expensive items.
A good check reconciles the grouped counts with a total computed on the same filtered population. Matching a number is evidence only when both queries measure the same thing.
SELECT category, COUNT(*) AS items FROM menu_items WHERE price >= 5 GROUP BY category HAVING COUNT(*) >= 2 ORDER BY category;
WHERE price >= 5 changes the input population first. GROUP BY collects those expensive items into categories, and HAVING keeps only categories with at least two remaining items.
Moving the count condition into WHERE is not equivalent. Removing WHERE is valid SQL but changes both the category counts and the business question.
Explore this concept →Explain it in your own words
Why would WHERE price >= 3 answer a different question?
SQL reference & course materials
Use these reference chapters alongside the detailed lessons above. Each example identifies its database.
Put it to work in class
Lab 3: The Monthly KPI Report →
Lab 5: Customer Segmentation →
Readiness practice and team activities complement the assigned Canvas lab. Official instructions and submission requirements stay in Canvas.