SQL Aggregates, Group By, Having, and Views — Lecture Notes

Session Notes: SQL Aggregates, Grouping, Having, and Views

  • Goal of the session: review bulk aggregate functions, the SELECT statement, and related topics (WHERE, ORDER BY, GROUP BY, HAVING), with practical examples from CSV loading and the Northwind database.
  • Context: Commands introduced in prior modules (info management); focus on advanced topics like WHERE, aggregate functions, GROUP BY, HAVING, and views.
  • Interaction style: instructor asks for student responses and clarifies concepts as they arise.

Basic data setup and SQL commands touched on in the lecture

  • Data setup and environment
    • Use a database (in the demo, a database named Northwind and a test database were mentioned).
    • To start, review databases: SHOW DATABASES.
    • Drop and recreate sample tables as needed during demonstrations.
    • A sample table (sample) with fields:
    • a: VARCHAR(1)
    • b: INT
    • Load data from a CSV file into the table using the syntax (note: path uses forward slashes, not backslashes):
    • "LOAD DATA INFILE 'path/to/file.csv' INTO TABLE sample FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ';'""
    • After loading data, display content with: SELECT * FROM sample;
  • Data concepts to recall
    • Excel analogy for sorting data and how SQL handles similar operations via ORDER BY.
    • Defaults: when you specify an ORDER BY on an alphanumeric column, the sort is ascending by default; you can specify ASC or DESC explicitly.
    • You can sort by multiple columns: first by the primary sort column, then by secondary columns (e.g., ORDER BY a, b or ORDER BY b, a).
    • If you omit ASC in a multi-column sort, the default is ascending for each specified column unless otherwise specified.

ORDER BY: sorting results in SQL

  • Syntax basics
    • SELECT <columns> FROM <table> ORDER BY <column(s)> [ASC|DESC], ...
    • If you don’t specify a sort direction, ascending order is assumed for each column (especially for alphanumeric data).
  • Examples and observations from the transcript
    • Sorting by column a alone yields rows in ascending order of column a (Alphanumeric: A-Z).
    • Sorting by column b alone shows ascending numeric order when you specify only column b (or when the data is numeric and no DESC/ASC is specified).
    • Sorting by multiple columns: first by column b, then by column a, or vice versa; inner logic: the first key sorts primarily, then ties are broken by the next key.
    • If you want a different alignment, you can switch the order of the sort keys (e.g., ORDER BY b, a vs ORDER BY a, b).
  • Practical note
    • Use uppercase for reserved keywords (SELECT, FROM, ORDER BY) to indicate SQL syntax clearly; user-defined identifiers stay in lowercase for readability.
  • Summary takeaway
    • ORDER BY controls the final order of rows; multiple keys allow multi-dimensional sorting; default is ascending unless DESC is specified.

Aggregate functions: overview and practical usage

  • What is an aggregate function? A function that summarizes multiple rows into a single value per group or for the entire result set.

  • Common aggregate functions discussed

    • COUNT: counts the number of rows (COUNT(*) counts all rows; COUNT(column) counts non-NULL values in a column).
    • SUM: sum of numeric values in a column.
    • AVG (average) / MEAN (synonymous in the lecture notes): arithmetic mean of numeric values.
    • MAX: maximum value in a numeric column.
    • MIN: minimum value in a numeric column.

  • Numeric vs non-numeric usage

    • Aggregate functions such as MAX, MIN, SUM, and AVG operate on numeric data.
    • Non-numeric fields cannot be aggregated with these functions (except when cast/converted, depending on the SQL dialect).

  • Example scenario from the transcript

    • Sample data with 24 rows; column b is numeric (integers).
    • With no WHERE filter:
    • Maximum of b: 9
    • Minimum of b: 1
    • Sum of b: 106
    • Average of b: approximately $4.4167$ (i.e., ar{b} = rac{ ext{Sum}(b)}{n} = rac{106}{24}

    )

    • With a filter WHERE b > 2:
    • Number of records: 18
    • Maximum of b: 9
    • Minimum of b: 3
    • Sum of b: 97
    • Average of b: approximately $5.3889$ (i.e., ar{b}_{>2} = rac{97}{18})

  • Column expressions and aliases

    • You can create expressions in SELECT using arithmetic with columns (e.g., b * 3 + 5) and give them aliases, like AS expression_one.
    • Example: SELECT MAX(b) AS max_b, AVG(b) AS avg_b, SUM(b) AS sum_b FROM sample;
    • You can also compute expressions with aggregates, e.g., MAX(b * 3 + 5) (depends on dialect and parentheses).

  • Aliases in SELECT for readability

    • Use meaningful aliases: e.g., MAX(b) AS max_b, AVG(b) AS avg_b.
    • If expressions are used, giving them descriptive aliases improves report clarity (e.g., SUM(b) AS total_b).

  • How aggregation interacts with WHERE and HAVING

    • WHERE clause filters rows before grouping and aggregation.
    • HAVING clause filters groups after aggregation has been computed.
    • Example narrative from the transcript:
    • First, filter rows with WHERE b > 2; then group by column a and compute aggregates per group.
    • Then apply HAVING to filter computed groups (e.g., having average greater than 4).
    • Alias usage in HAVING: you can reference an alias for an aggregated expression in HAVING (depending on dialect), or you can repeat the full aggregate expression in HAVING (e.g., HAVING AVG(b) > 4).

  • Aggregates with multiple groups

    • You can group by more than one column, generating hierarchical groups (e.g., first by column a, then by another column).
    • Example narrative: to compute per-letter maxima or per-month averages, group by a primary key (e.g., salesperson, date) and then apply aggregates within each group.

  • Using a view to store aggregated summaries

    • CREATE VIEW example: CREATE OR REPLACE VIEW summary_sample AS SELECT COUNT(*), MAX(b), AVG(b), SUM(b) FROM sample;
    • A view acts as a virtual table; you can query it like SELECT * FROM summary_sample;
    • Views are useful for reporting layers in applications (e.g., C#, PHP, Python front-ends) without storing derived data physically.
    • Notes on data types in expressions:
    • Aggregates like AVG generally yield decimal results.
    • MAX/MIN/SUM on integers yield integer results; arithmetic within expressions may yield larger types depending on the dialect (e.g., big integer if needed).

  • Practical SQL structure and best practices

    • SQL clause order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
    • Use aliases for readability; prefer explicit alias names for aggregated values in the SELECT clause.
    • When computing an aggregate with a filter, apply WHERE first, then GROUP BY, then apply HAVING to the groups.
    • You can create reusable views to expose pre-aggregated data or summaries for reports and UIs.

GROUP BY: aggregating per group

  • Purpose: summarize data per distinct value(s) of one or more columns.
  • Example narrative from the lecture
    • Group by column a to summarize across each distinct value of a.
    • Within each group, compute aggregates like COUNT(*), MAX(b), and AVG(b) (and others) to describe the group.
    • Semantic flow: rows are partitioned by the grouping keys; aggregates are computed per group.
  • Example query (conceptual)
    • To get the number of records per value of a and the max/average of b within each group:
    • SELECT a, COUNT(*) AS total, MAX(b) AS max_b, AVG(b) AS avg_b FROM sample GROUP BY a;
  • Filtering groups with HAVING
    • After grouping, you can filter groups that meet a condition on an aggregate, e.g.:
    • HAVING AVG(b) > 4 to keep only groups where the average of b exceeds 4.
    • You can also place a condition on a grouped alias (depending on dialect): e.g., HAVING AVG(b) > 4 or HAVING max_b > 4 if you aliased it and the dialect supports it.
  • Example narrative about where vs having
    • Where clause filters rows prior to grouping; having filters groups after aggregation.
    • This difference explains why some groups disappear after applying a HAVING condition that would have included them before grouping (e.g., when some rows do not meet the WHERE condition).

Subqueries and the Northwind example: practical reporting

  • Subqueries (brief mention in the lecture)
    • The lecturer indicated subqueries would be discussed later; they were not explored in-depth in this session.
  • Northwind database usage
    • Northwind contains multiple related tables (orders, order details, employees, etc.).
    • Example tasks in Northwind:
    • Find how many orders each employee attended during the entire period: group by EmployeeID in the Orders table and count orders.
    • Extend to per-day granularity: group by EmployeeID and OrderDate to count orders per employee per day.
    • Conceptual join requirement when aggregating sales data: to compute total quantity or total sales per product per employee, join Orders with Order Details and optionally with Products to bring in product names and categories.
  • Practical takeaway
    • Grouping and aggregation become powerful when combined with joins and subqueries for complex reports (e.g., per-employee daily sales, per-category product counts).
  • Example of typical end-user report usage
    • You can save a complex aggregation as a view and query the view for a front-end application (e.g., to populate a dashboard).

Practical exercise: counting products per category (as discussed in class)

  • Prompt from the instructor
    • Given a table products with a column category, determine how many products exist per category.
  • Suggested query (complete statement)
    • SELECT category, COUNT(*) AS total_products FROM products GROUP BY category;
  • Expected interpretation
    • The result shows each category (e.g., Beverages, Condiments, Oils, etc.) and the number of products in that category.
  • Classroom interaction note
    • Students were asked to volunteer the exact query; the instructor guided the process of forming the correct GROUP BY clause and the COUNT aggregate.

Key SQL keywords and typical order of clauses

  • Order of clauses in a typical query:
    • SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
  • Example of applying the sequence in practice
    • Start with selecting the desired columns or expressions (including aggregates and aliases).
    • Apply a FROM clause to identify the source tables.
    • Apply WHERE to filter rows before grouping.
    • Use GROUP BY to group rows for aggregation.
    • Apply HAVING to filter groups after aggregation.
    • Finally, apply ORDER BY to sort the resulting groups or rows.
  • Example adjustments
    • If you want to sort by a computed aggregate (e.g., the max of a column), you can use ORDER BY MAX(b) DESC or reference an alias like ORDER BY max_b DESC if your dialect supports the alias in ORDER BY.

Distinctions and practical tips highlighted in the session

  • Literals and quotes
    • Numeric literals do not require quotes.
    • Non-numeric literals (alphanumeric, strings) should be enclosed in single quotes: e.g., 'Bev' or 'Oil'.
  • Expressions in SELECT
    • You can include arithmetic expressions involving columns and functions, and give them aliases for readability.
    • Examples include arithmetic combinations like b * 3 + 5 and incorporated into an aggregate expression or as a simple column expression.
  • Views vs tables
    • A view is a stored query that appears like a table but does not physically store data; it derives its result from the base table(s).
    • You can create a view for a reporting module and query it as if it were a table: CREATE VIEW summary AS SELECT ... FROM sample; and SELECT * FROM summary;
    • To remove a view, use DROP VIEW view_name (not DROP TABLE).
  • Data type considerations in aggregates
    • Aggregates like MIN, MAX, and SUM operate on numeric fields and return numeric results.
    • AVG yields a decimal value; the exact resulting type is implementation-dependent but typically a decimal or floating-point type.
    • Expressions involving integers can return larger integer types depending on the dialect (e.g., BIGINT in MySQL).
  • Practical programming guidance from the session
    • When building reports in a program (C#, PHP, Python, etc.), treating a view as a stable source of summarized data can simplify front-end code.
    • Breaking complex calculations into a view helps with maintainability and reuse across different reports.
  • Summary of the educational goal
    • Mastery of: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, aggregate functions, aliases, expressions, and views; understanding the order of operations and practical reporting uses.
  • Administrative note from the session
    • The instructor emphasized reviewing slides, practicing with Northwind-related examples, and preparing for a lab quiz with practical SQL queries.

Key formulas and equations (LaTeX)

  • Mean (average) of a numeric column b over n rows:
    • bˉ=extSUM(b)n\bar{b} = \frac{ ext{SUM}(b)}{n}
  • General relationship for aggregates (conceptual):
    • Number of rows in a group: n=extCOUNT(∗)n = ext{COUNT}(*)
    • Sum of a column within a group: extSUM(b)ext{SUM}(b)
    • Maximum within a group: extMAX(b)ext{MAX}(b)
    • Minimum within a group: extMIN(b)ext{MIN}(b)
    • Average within a group: extAVG(b)ext{AVG}(b) (sometimes written as MEAN(b) in notes)
  • Example query structure (conceptual):
    • extSELECTa,extCOUNT(∗)extAStotal,extMAX(b)extASmax<em>b,extAVG(b)extASavg</em>b extFROMsample extGROUPBYa;ext{SELECT } a, ext{COUNT}(*) ext{ AS total}, ext{MAX}(b) ext{ AS max<em>b}, ext{AVG}(b) ext{ AS avg</em>b} \ ext{FROM sample} \ ext{GROUP BY } a;
  • Filtering after grouping (HAVING):
    • ext{HAVING } ext{AVG}(b) > 4
  • View creation (conceptual):
    • extCREATEVIEWsummaryASSELECTCOUNT(∗),MAX(b),extAVG(b),extSUM(b)extFROMsample;ext{CREATE VIEW summary AS SELECT COUNT(*), MAX}(b), ext{AVG}(b), ext{SUM}(b) ext{ FROM sample};

Quick study tips drawn from the session

  • Practice sequencing: remember the canonical clause order and how WHERE precedes GROUP BY while HAVING follows GROUP BY.
  • Use descriptive aliases for all non-trivial expressions to improve readability in reports.
  • Experiment with multi-column sorting and sorting by computed aggregates, where supported.
  • Build small, reusable views to encapsulate common summaries for front-end code.
  • For counting per category or similar problems, start with SELECT category, COUNT(*) AS total FROM products GROUP BY category;

Connections to foundational principles and real-world relevance

  • Aggregation mirrors how humans summarize data in spreadsheets and reports; SQL provides explicit, scalable ways to perform these summaries on large datasets.
  • Grouping aligns with the concept of grouping data by category, time period, department, etc., to compute descriptive statistics per group.
  • The distinction between WHERE and HAVING reflects a fundamental data processing pipeline: filtering data first, then aggregating, then optionally filtering the results of the aggregation.
  • Views support separation of concerns in software: data retrieval logic (queries) can be centralized in the database layer, enabling cleaner application code and consistent reporting outputs.

Important reminders from the lecturer

  • If you were absent, please review the uploaded slides and the MS Teams materials to catch up.
  • Prepare for a laboratory activity and a follow-up quiz; practice forming complete, syntactically correct queries that use the discussed constructs.
  • Attend to the examples with Northwind and the sample data to reinforce understanding of aggregation and grouping.