Aggregate Queries
Aggregate Queries Kathi Kellenberger [email protected]
BIO • DBA at Bryan Cave LLP • SQL Server MVP • Author – Beginning T-SQL 2008 (Apress 2009) – SQL Server MVP Deep Dives (Manning 2009) – Professional SQL Server 2005 Integration Services (Wrox 2006)
• Speaker • Trainer
Agenda • • • • • • •
What are aggregate queries? Aggregate functions GROUP BY HAVING More stuff to keep in mind Review Resources
What Are Aggregate Queries? • The way to summarize data – Over all rows – Over a group, for example dept or territory
• Adding any feature of an aggregate query makes it an aggregate query
Aggregate Functions • • • • • •
COUNT COUNT_BIG AVG MIN MAX Others…
Aggregate Functions • Options – ALL, the default – DISTINCT
• COUNT – * to count the rows
• Functions ignore NULL values – Watch out when using AVG!
DEMO 1
GROUP BY • Allows aggregate functions to be applied over groups • Follows the FROM or WHERE clause if any • Can group on expressions, too • RULE: Any non-aggregated column in the SELECT list or ORDER BY clause must be in the GROUP BY clause!
DEMO 2
HAVING • Use to filter after the grouping has been applied • Filter aggregate expressions • Don’t filter aggregate expressions in the WHERE clause • The database engine may rearrange if you put a non-aggregated column in the HAVING clause
Having
DEMO 3
More Stuff • Use in any type of SELECT statement • Can’t update with an aggregate query – Use another method, like CTE, temp table, etc.
• The OVER clause • Grouping Sets, CUBE and ROLLUP
DEMO 4
Review • Adding any aspect of an aggregate query makes it an aggregate query • Any non-aggregated column in the SELECT list or ORDER BY must be in the GROUP BY clause • Aggregate functions ignore NULLs • Use WHERE to filter non-aggregated columns or expressions • Use HAVING to filter aggregate expressions
Resources • • • • • • •
Beginning T-SQL 2008 (Apress) SQLServerCentral.com SQLShare.com SQLTeam.com Simple-talk.com Codeplex.com (search for AdventureWorks) Inside series by Itzik Ben-Gan (MS Press)
BIO • DBA at Bryan Cave LLP • SQL Server MVP • Author – Beginning T-SQL 2008 (Apress 2009) – SQL Server MVP Deep Dives (Manning 2009) – Professional SQL Server 2005 Integration Services (Wrox 2006)
• Speaker • Trainer
Agenda • • • • • • •
What are aggregate queries? Aggregate functions GROUP BY HAVING More stuff to keep in mind Review Resources
What Are Aggregate Queries? • The way to summarize data – Over all rows – Over a group, for example dept or territory
• Adding any feature of an aggregate query makes it an aggregate query
Aggregate Functions • • • • • •
COUNT COUNT_BIG AVG MIN MAX Others…
Aggregate Functions • Options – ALL, the default – DISTINCT
• COUNT – * to count the rows
• Functions ignore NULL values – Watch out when using AVG!
DEMO 1
GROUP BY • Allows aggregate functions to be applied over groups • Follows the FROM or WHERE clause if any • Can group on expressions, too • RULE: Any non-aggregated column in the SELECT list or ORDER BY clause must be in the GROUP BY clause!
DEMO 2
HAVING • Use to filter after the grouping has been applied • Filter aggregate expressions • Don’t filter aggregate expressions in the WHERE clause • The database engine may rearrange if you put a non-aggregated column in the HAVING clause
Having
DEMO 3
More Stuff • Use in any type of SELECT statement • Can’t update with an aggregate query – Use another method, like CTE, temp table, etc.
• The OVER clause • Grouping Sets, CUBE and ROLLUP
DEMO 4
Review • Adding any aspect of an aggregate query makes it an aggregate query • Any non-aggregated column in the SELECT list or ORDER BY must be in the GROUP BY clause • Aggregate functions ignore NULLs • Use WHERE to filter non-aggregated columns or expressions • Use HAVING to filter aggregate expressions
Resources • • • • • • •
Beginning T-SQL 2008 (Apress) SQLServerCentral.com SQLShare.com SQLTeam.com Simple-talk.com Codeplex.com (search for AdventureWorks) Inside series by Itzik Ben-Gan (MS Press)
