Lab3 aggregating data
Download as PPTX, PDF
0 likes61 views
Group functions operate on sets of rows to aggregate data and return a single result per group. Common group functions include AVG, COUNT, MAX, MIN, and SUM. The GROUP BY clause is used to split rows into groups and is required when non-aggregate columns are selected. The HAVING clause allows filtering groups based on conditions and is analogous to the WHERE clause for rows. Nesting group functions allows aggregating aggregates, like finding the maximum average salary.
![Using Group Functions
Syntax:
SELECT [column, ] group_function(column)
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];](https://image.slidesharecdn.com/lab3-aggregatingdata-210914205441/85/Lab3-aggregating-data-4-320.jpg)
![Creating Groups of Data:
GROUP BY Clause
Divide rows in a table into smaller groups by using th
GROUP BY clause.
SELECT [column, ] group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
group_by_expression specifies columns whose values
determine the basis for grouping
rows.](https://image.slidesharecdn.com/lab3-aggregatingdata-210914205441/85/Lab3-aggregating-data-10-320.jpg)
![Excluding Group Results:
Having Clause
Use the HAVING clause to restrict groups
Rows are grouped.
The group function is applied.
Groups matching the HAVING clause are displayed.
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];](https://image.slidesharecdn.com/lab3-aggregatingdata-210914205441/85/Lab3-aggregating-data-18-320.jpg)
![Summary
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
Order of evaluation of the clauses:
WHERE clause
GROUP BY clause
HAVING clause](https://image.slidesharecdn.com/lab3-aggregatingdata-210914205441/85/Lab3-aggregating-data-22-320.jpg)
Lab3 aggregating data
- 1. AGGREGATING DATA USING GROUP FUNCTIONS Structured Query Language
- 2. Group Functions What are group functions ? Group functions operate on sets of rows to give one result per group. These sets may be the whole table or the table split into groups. maximum salary in the EMP table
- 3. Types of Group Functions AVG COUNT MAX MIN SUM Function Description AVG(DISTINCT|ALL|n) Average value of n, ignoring null values COUNT({*|DISTINCT|ALL|expr}) Number of rows, where expr evaluates to something other than null (Count all rows using *, including duplicates and rows with nulls) MAX(DISTINCT|ALL|expr) Maximum value of expr, ignoring null values MIN(DISTINCT|ALL|expr) Minimum value of expr, ignoring null values SUM(DISTINCT|ALL|n) Sum values of n, ignoring null values
- 4. Using Group Functions Syntax: SELECT [column, ] group_function(column) FROM table [WHERE condition] [GROUP BY column] [ORDER BY column];
- 5. Using AVG and SUM Functions We can use AVG, SUM, MIN, and MAX functions against columns that can store numeric data. The example displays the average, highest, lowest, and sum of monthly salaries for all salespeople.
- 6. Using MIN and MAX Functions We can use MIN and MAX for any data type.
- 7. Using COUNT Function COUNT(*) returns the number of rows in a table COUNT(expr) returns the number of non-null rows.
- 8. Using COUNT Function (continued) Display the number of departments in the EMP table. Display the number of distinct departments in the EMP table.
- 9. Creating Groups of Data We have used the group functions on the tables as one large group of information. At times we need to divide the table into smaller groups. This can be done by using GROUP BY clause. EMP average salary in EMP table for each department 2916.6667 2175 1566.6667
- 10. Creating Groups of Data: GROUP BY Clause Divide rows in a table into smaller groups by using th GROUP BY clause. SELECT [column, ] group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]; group_by_expression specifies columns whose values determine the basis for grouping rows.
- 11. Using the GROUP BY Clause All columns int the SELECT list that are not in group functions must by in the GROUP BY clause. The GROUP BY column does not have to be in the SELECT list.
- 12. Grouping by More Than One Column EMP sum salaries in the EMP table for each job, grouped by department Display the total salary being paid to each job title, within each department.
- 13. Using the GROUP BY Clause on Multiple Columns Display the total salary being paid to each job title, within each department.
- 14. Illegal Queries Using Group Functions Any column or expression in the SELECT list that is not an aggregate function, must be in the GROUP BY clause. Display the number of employees in each department.
- 15. Illegal Queries Using Group Functions We cannot use the WHERE clause to restrict groups. We use the HAVING clause to restrict groups. Display the average salaries of those departments that have an average salary greater than $2000.
- 16. Excluding Group Results In the same way that we use the WHERE clause to restrict rows that we select, we use the HAVING clause to restrict groups. To find the maximum salary of each department, but show only the departments that have maximum salary more than $2900, we need to do the following: Find the maximum salary for each department by grouping by department number. Restrict the groups to those departments with a maximum salary greater than $2900.
- 17. Excluding Group Results EMP 5000 3000 2850 maximum salary per department greater than $2900
- 18. Excluding Group Results: Having Clause Use the HAVING clause to restrict groups Rows are grouped. The group function is applied. Groups matching the HAVING clause are displayed. SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];
- 19. Using the HAVING Clause Display department numbers and maximum salary for those department whose maximum salary is greater than $2900.
- 20. Display the job title and total monthly salary for each job title with a total payroll exceeding $5000. Exclude salespeople and sort the list by the total monthly salary. Using the HAVING Clause
- 21. Nesting Group Functions Display the maximum average salary.
- 22. Summary SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; Order of evaluation of the clauses: WHERE clause GROUP BY clause HAVING clause