LES05使用分组函数汇总数据.pptVIP

使用分组函数汇总数据 目标 描述常用分组函数 使用GROUP BY子句分组数据 使用HAVING子句排除分组行 使用CUBE和ROLLUP操作符 什么是分组函数 分组函数：操纵行集，并且每组产生一个结果 分组函数类型 AVG 平均值 COUNT 总计 MAX 最大 MIN 最小 STDDEV 平方 SUM 和 VARIANCE 求根 使用分组函数 使用AVG和SUM函数 AVG、SUM：用于数字数据 使用MIN和MAX函数 MIN、MAX：使用于任何数据类型 使用COUNT函数 COUNT(*) ：返回总计行数 使用 COUNT函数 COUNT(expr) ：返回NOT NULL行数 建立分组数据 建立分组数据：GROUP BY子句 使用 GROUP BY子句 多列分组 使用GROUP BY子句进行多列分组 使用分组函数的非法查询 使用分组函数的非法查询 排除分组结果 排除分组结果：HAVING子句 小结 学会使用GROUP BY子句和HAVING子句 习题 分组函数统计多行数据，并生成一个结果？TRUE/FALSE 在使用分组函数运算时包含NULL？TRUE/FALSE 显示所有雇员的工资总和、最大工资、最小工资以及平均工资 显示每个岗位雇员的工资总和、最大工资、最小工资以及平均工资 编写查询显示每个岗位的雇员总数 习题 显示管理者的总数 显示雇员工资的最大差额 Groups of Data Until now, all group functions have treated the table as one large group of information. At times, you need to divide the table of information into smaller groups. This can be done by using the GROUP BY clause. 默认情况下group by后的Columm按升序排列 The GROUP BY Clause You can use the GROUP BY clause to divide the rows in a table into groups. You can then use the group functions to return summary information for each group. In the syntax: group_by_expression specifies columns whose values determine the basis for grouping rows. Guidelines If you include a group function in a SELECT clause, you cannot select individual results as well unless the individual column appears in the GROUP BY clause. You will receive an error message if you fail to include the column list. Using a WHERE clause, you can preexclude rows before dividing them into groups. You must include the columns in the GROUP BY clause. You cannot use the column alias in the GROUP BY clause. By default, rows are sorted by ascending order of the columns included in the GROUP BY list. You can override this by using the ORDER BY clause. The GROUP BY Clause (continued) When using the GROUP BY clause, make sure that all columns in the SELECT list that are not in the group functions are included in the GROUP BY clause. The above example displays the department number and the average salary for each department. Here