ISYS1055 Chap.9 Grouped Aggregation, Set Operations and Views
Grouped Aggregation, Set Operations and Views
Week 7 is SQL continued, and it has four stated outcomes: use grouped aggregation and apply selection criteria to grouped data, perform set operations, explain when a view is appropriate and how it differs from a table, and define how dates and times are handled in SQL. The distinction that is actually marked is WHERE against HAVING - one filters rows before grouping, the other filters whole groups afterwards - and the set operators bring a second way of combining result sets. Date and time handling is the single exception the course makes to its SQL-92 scope, and it varies by platform, so it is taught on SQLite with Oracle and SQL Server shown only for contrast. Week 7 also carries quiz 3, in your practical.
What this chapter covers
- 01GROUP BY: partition the rows that survived WHERE into groups with a unique combination of grouping values, then compute each aggregate once per group
- 02The SELECT-list rule for grouped queries - only grouping attributes and aggregate functions - and the explicit warning that SQLite does not enforce it while other engines reject the query
- 03HAVING filters whole groups after grouping and must always accompany a GROUP BY; WHERE filters individual rows before it
- 04Reading a grouped query in evaluation order: join, filter rows, group, aggregate, filter groups, project, sort
- 05Set operations UNION, INTERSECT and EXCEPT over two whole result sets, with their Venn and boolean readings, the column-compatibility requirement, and the dialect note that Oracle spells EXCEPT as MINUS while SQLite uses EXCEPT
- 06Views as named stored queries that behave like virtual tables: structuring data naturally, restricting access, and summarising across tables for reporting
- 07View versus table, and why a view is generally not updatable - the hook into INSTEAD OF triggers
- 08Dates and times in SQLite: no dedicated storage class, so values are held as ISO-8601 text, a Julian day number, or Unix time, with DATE(), TIME(), DATETIME(), JULIANDAY() and STRFTIME() to read and format them
WHERE versus HAVING on the same table, then wrap the result as a view
- +1(a) Group the rows and aggregate once per group, keeping only grouping attributes and aggregates in the SELECT list. SELECT therapistID, COUNT(*) AS "Completed" / FROM appointment / WHERE status = 'completed' / GROUP BY therapistID; - the WHERE clause runs before the grouping, so cancelled and no-show rows never reach a group and never influence a count.
- +1(b) Filter the groups, not the rows. HAVING applies after grouping and after the aggregate has been computed, and it must accompany a GROUP BY: add HAVING COUNT(*) > 5 below the GROUP BY line. Read the finished query in evaluation order - filter rows, group, count, then discard the groups whose count is five or less.
- +1(c) Moving the status test into HAVING changes the answer, because the grouping would then be computed over all appointments regardless of status, and HAVING can only test the group as a whole. The row-level condition on status belongs in WHERE; only conditions on an aggregate belong in HAVING. This pair of queries is the standard way the distinction is assessed: the same data, two placements, two different results.
- +1(d) A view is a named stored query - it holds a definition rather than data - and is then used exactly like a table: CREATE VIEW busy_therapist (therapistID, completed) AS SELECT therapistID, COUNT(*) FROM appointment WHERE status = 'completed' GROUP BY therapistID HAVING COUNT(*) > 5; followed by SELECT * FROM busy_therapist;. Note that this view is built on grouped aggregation, so it is not updatable - there is no well-defined way to map a change to a summary row back onto the underlying rows.
Key terms
- GROUP BY
- Partitions the rows selected by the WHERE clause into groups, one per unique combination of the grouping attributes, and computes each aggregate function once per group. Only grouping attributes and aggregates may appear in the SELECT list.
- HAVING
- Filters whole groups after grouping and aggregation, and must always accompany a GROUP BY. The division of labour is fixed: row conditions in WHERE before grouping, aggregate conditions in HAVING after it.
- UNION, INTERSECT, EXCEPT
- Set operations over two whole result sets: rows in either, rows in both, and rows in the first but not the second. They require column compatibility - the same number of columns of corresponding types - and Oracle spells EXCEPT as MINUS, which is a non-standard variant rather than the course standard.
- View
- A virtual table defined by a stored query. It holds no data of its own, only a definition, a name and a derived schema; when queried, the engine substitutes the definition. Views structure data naturally for users, restrict what a user can see, and package a reporting join or summary so it need not be rewritten.
- Why a view is not updatable
- A view stores no values, so an INSERT, UPDATE or DELETE against it has no rows of its own to change. Where the mapping back to base rows is well defined a trigger can be written to do the work; where the view aggregates or groups, that mapping may not exist at all.
- SQLite date and time storage
- SQLite has no date or time storage class, so values are held as ISO-8601 text, as a real Julian day number, or as an integer Unix time, and are read with DATE(), TIME(), DATETIME(), JULIANDAY() and formatted with STRFTIME(). This is the one place the course steps outside its SQL-92 scope, and the handling differs on every platform.
Grouped Aggregation, Set Operations and Views FAQ
What is the difference between WHERE and HAVING?
WHERE filters individual rows before any grouping happens; HAVING filters whole groups after the aggregate has been computed, and cannot be used without a GROUP BY. If the condition is about a column value, it belongs in WHERE. If it is about a count, sum or average of a group, it belongs in HAVING. Moving a row condition into HAVING quietly changes the answer, which is exactly why the pair is assessed together.
Why does SQLite accept a GROUP BY query that other engines reject?
The standard says only grouping attributes and aggregates may appear in the SELECT list, because any other attribute has no single value within a group. SQLite does not enforce that rule and returns an arbitrary value from the group instead of an error, while an engine such as Oracle rejects the statement. The course flags this explicitly: write standard-conformant grouped queries, because a query that only works because of a permissive engine is a defect you will not see until it matters.
When should I create a view instead of repeating a query?
When the same inner query keeps reappearing, when different users should see only part of a table, or when a reporting summary would otherwise be rebuilt by hand every time. A view is queried exactly like a table, stays consistent with the underlying tables because it stores no data, and is the natural home for a join you have already written twice.
How do I store a date in SQLite if there is no date type?
You choose the representation. ISO-8601 text sorts correctly and is readable; a real Julian day number supports fractional days and arithmetic; an integer Unix time is compact. Convert on the way in with DATETIME() or JULIANDAY(), and read them back with DATE(), TIME() or STRFTIME() with a format string - selecting a Julian day raw just returns a number. Because this varies so much between platforms, the standing advice is to check the manual of whichever engine you are using.
Assessment move
Drill the WHERE-versus-HAVING pair until it is reflex: take one table and write the two queries that differ only in where the condition sits, run both, and articulate why the numbers differ. Then practise reading any grouped query in evaluation order rather than in written order, because that is what makes the SELECT-list rule feel inevitable instead of arbitrary. Write at least one query using each set operator, and prove the equivalence the course likes to show - a range expressed with BETWEEN, and again with INTERSECT or EXCEPT over two queries. Build two views, one summary and one narrowed, and then deliberately try to update each so you feel the limitation that Week 8's INSTEAD OF triggers exist to solve. Quiz 3 falls in this week's practical and covers Weeks 5 and 6, so keep the earlier single-table and join material warm as you go.
Working through Grouped Aggregation, Set Operations and Views in ISYS1055? Sia is AskSia’s AI Information Technology tutor — ask any ISYS1055 Grouped Aggregation, Set Operations and Views question and get a clear, step-by-step explanation grounded in how ISYS1055 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
