Aggregate FunctionsSlide 2Behavior of Aggregate function Continued…Types of SQL Aggregate FunctionsInput to Aggregate FunctionStaffSUM()AVG()Revised Query for AVG()Slide 10MIN() and MAX()COUNT()Use of COUNT() and SUM()COUNT() and SUM() continuedSlide 15COUNT(*)COUNT(*) continued…Usage of Aggregation FunctionsUse of GROUP BYSlide 20GROUP BYSQL’S ROLEUSE OF HAVINGHaving Clause continued….ReferencesAggregate FunctionsAggregate FunctionsPresenter: Sushma BandekarPresenter: Sushma BandekarCS 157(A) – Fall 2006CS 157(A) – Fall 2006Aggregate Function:Aggregate functions are functions that take a collection of values as input and return a single value. Behavior of Aggregate Functions:Operates - on a single column Return - a single value.Used only in the SELECT list and in the HAVING clause.Behavior of Aggregate function Behavior of Aggregate function Continued…Continued…Accepts: DISTINCT : consider only distinct values of the argument expression. ALL : consider all values including all duplicates.Example: SELECT COUNT( DISTINCT column_name)Types of SQL Aggregate FunctionsTypes of SQL Aggregate FunctionsSUM SUM AVGAVGMINMINMAXMAXCOUNTCOUNTInput to Aggregate FunctionInput to Aggregate FunctionSUM and AVG :SUM and AVG : Operates only on collections of numbers .Operates only on collections of numbers .MIN , MAX and COUNTMIN , MAX and COUNT Operates on collection of numeric and non-numeric Operates on collection of numeric and non-numeric data types.data types.Each function eliminates NULL values and operates on Each function eliminates NULL values and operates on Non- null values.Non- null values.StaffStaffsnosnofnamefnamelnamelnamesalarysalarypositionpositionSL100SL100JohnJohnWhiteWhite30000.0030000.00ManagerManagerSL101SL101Susan Susan BrandBrand24000.0024000.00ManagerManagerSL102SL102DavidDavidFordFord12000.0012000.00Project Project ManagerManagerSL103SL103AnnAnnBeechBeech12000.0012000.00Project Project ManagerManagerSL104SL104MaryMaryHoweHowe9000.009000.00Project Project ManagerManagerSUM()SUM()Returns: The sum of the values in a specified column.Example: Find the total/sum of the Managers salary Query:SELECT SUM( salary) AS sum_salaryFROM StaffWHERE Staff.position = ‘Manager’;Result: sum_salary 54000.00AVG()AVG()Returns: The average of the values in a specified column.Example: Find the average of the Project Managers salary .Query:SELECT AVG( DISTINCT salary) AS avg_salaryFROM StaffWHERE Staff.position = ‘Project Manager’;Result: avg_salary 10500.00 // Error in Result // avg_salary = 11000.00 // What is wrong?Revised Query for AVG()Revised Query for AVG()Query: SELECT AVG(ALL salary) AS avg_salaryFROM StaffWHERE Staff.position = ‘Project Manager’;Result : avg_salary 11000.00CAUTION: Using DISTINCT and ALL in SUM() and AVG()StaffStaffsnosnofnamefnamelnamelnamesalarysalarypositionpositionSL100SL100JohnJohnWhiteWhite30000.0030000.00ManagerManagerSL101SL101Susan Susan BrandBrand24000.0024000.00ManagerManagerSL102SL102DavidDavidFordFord12000.0012000.00Project Project ManagerManagerSL103SL103AnnAnnBeechBeech12000.0012000.00Project Project ManagerManagerSL104SL104MaryMaryHoweHowe9000.009000.00Project Project ManagerManagerMIN() and MAX()MIN() and MAX()Returns: MIN() returns the smallest value of a column. MAX() returns the largest value of a column.Example: Find the minimum and maximum staff salary.Query:SELECT MIN( salary) AS min_salary, MAX (salary) AS max_salaryFROM Staff;Result: min_salary max_salary 9000.00 30000.00COUNT()COUNT()Returns: The number of values in the specified column. Example: Count number of staffs who are Manager.Query: SELECT COUNT(sno) AS sno_countFROM StaffWHERE Staff.position = ‘Manager’;Result: sno_count 2Use of COUNT() and SUM()Use of COUNT() and SUM()Example: Find the total number of Managers and the sum of there salary.Query: SELECT COUNT( sno) AS sno_count , SUM(salary) AS sum_salary From Staff WHERE Staff.position = ‘Manager’;snosnofnamefnamelnamelnamesalarysalarypositionpositionSL100SL100JohnJohnWhiteWhite30000.0030000.00ManagerManagerSL101SL101SusanSusanBrandBrand24000.0024000.00ManagerManagerSUMCOUNTCOUNT() and SUM() continuedCOUNT() and SUM() continuedResult:Result:sno_countsno_countsum_salarysum_salary2254000.0054000.00StaffStaffsnosnofnamefnamelnamelnamesalarysalarypositionpositionSL100SL100JohnJohnWhiteWhite30000.0030000.00ManagerManagerSL101SL101Susan Susan BrandBrand24000.0024000.00ManagerManagerSL102SL102DavidDavidFordFord12000.0012000.00Project Project ManagerManagerSL103SL103AnnAnnBeechBeech12000.0012000.00Project Project ManagerManagerSL104SL104MaryMaryHoweHowe9000.009000.00Project Project ManagerManagerCOUNT(*)COUNT(*)Input: There is no input to this function.Returns: It counts all the rows of a table , regardless of whether Nulls or the duplicate occur.Example: How many Project Manager salary is more than 9000.00Query: SELECT COUNT(*) AS Count_Salary FROM Staff WHERE Staff.position = ‘Project Manager’ AND Staff.salary > 9000.00COUNT(*) continued…COUNT(*) continued…Result:Result:Count_ SalaryCount_ Salary 22Usage of Aggregation FunctionsUsage of Aggregation FunctionsUse of GROUP BYUse of GROUP BYUse of HAVINGUse of HAVINGUse of GROUP BYUse of GROUP BY• GROUP BY: It groups the data from the SELECT table and produce a single summary row for each group• When Using GROUP BY:1. Each item in the SELECT list must be single valued per group.2. SELECT clause may only contain:Columns namesAggregate functionConstantsAn expression involving combinations of the above.3. All column names in SELECT list must appear in the GROUP BY clause unless the name is used only in the aggregate function.StaffStaffsnosnobnobnofnamefnamelnamelnamesalarysalarypositionpositionSL100SL100B3B3JohnJohnWhiteWhite30000.0030000.00ManagerManagerSL101SL101B5B5Susan Susan BrandBrand24000.0024000.00ManagerManagerSL102SL102B3B3DavidDavidFordFord12000.0012000.00Project Project ManagerManagerSL103SL103B5B5AnnAnnBeechBeech12000.0012000.00Project Project ManagerManagerSL104SL104B7B7MaryMaryHoweHowe9000.009000.00Project Project ManagerManagerGROUP BYGROUP BYExample: Find the number of staff
View Full Document
Unlocking...