Overview
This lecture focuses on the "having" statement in SQL, explaining how it differs from "where" and how it is used to filter grouped query results.
Review of Group By
- The "group by" statement groups similar records and allows for aggregate calculations like count, sum, or average.
- Example: Aggregating the number of flights, ticket spending, and miles earned per passenger.
Introduction to Having Statement
- The "having" statement is used after "group by" to filter grouped results based on aggregate criteria.
- Example: "having count() >= 6" filters for passengers with at least 6 flights.
Combining Multiple Conditions in Having
- Multiple conditions can be combined in a single "having" clause (e.g., minimum flights and minimum total ticket price).
- Example: "having count() >= 6 and sum(ticket_price) >= 5000" filters for passengers meeting both criteria.
Difference Between Where and Having
- The "where" clause filters raw data before grouping.
- The "having" clause filters aggregated results after the "group by" operation.
- Both "where" and "having" can be used in the same query for different filtering purposes.
Example: Using Where and Having Together
- "where" can filter for rows where "miles > 0" to include only frequent flyer club members.
- Changing "where" to "miles > 10000" may result in no results after aggregation if no group meets all conditions.
Key Terms & Definitions
- Group By — SQL clause that groups rows sharing a property so aggregate functions can be applied.
- Having — SQL clause used to filter results after "group by" based on aggregate functions.
- Where — SQL clause used to filter records before any grouping or aggregation.
- Aggregate Function — Functions like COUNT, SUM, or AVG that perform calculations on multiple rows.
Action Items / Next Steps
- Review the distinct function in SQL, which will be demonstrated in the next lecture.
- Practice writing SQL queries using both "where" and "having" clauses.