Writing Group Functions - DBMS

Writing Group Functions - DBMS

• 1.
  Name: Solaiman Hridoy Roll:17010115 Course Title: CSE 226 Database Management Systems Lab Batch: 29th (4th Semester) Date: 28-11-2018 Topic: Writing Group functions
• 2.
  Types of GroupFunctions  AVG  COUNT  MAX  MIN  STDDEV  SUM  VARIANCE
• 3.
  Group Functions AVG, SUM,MIN, and MAX functions can be used against columns that can store numeric data. The example in the image displays the average, highest, lowest, and sum of monthly salaries for all sales representatives.
• 4.
  Min and MaxFunction The MIN and MAX functions can be used for any data type. The following image displays the employee last name that is first and the employee last name that is the last in an alphabetized list of all employees.
• 5.
  Min and MaxFunction(Another Example) The below example image displays the most junior and most senior employee.
• 6.
  The COUNT Function TheCOUNT function has three formats:  COUNT(*), COUNT(expr), COUNT(DISTINCT expr)  COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns. If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfies the condition in the WHERE clause.
• 7.
  COUNT(expr) returns thenumber of nonnull values in the column identified by expr. COUNT(DISTINCT expr) returns the number of unique, non-null values in the column identified by expr.
• 8.
  Using the NVLFunction with Group Functions The NVL function forces group functions to include null values. In the image, the average is calculated based on all rows in the table, regardless of whether null values are stored in the COMMISSION_PCT column. The average is calculated as the total commission paid to all employees divided by the total number of employees in the company.
• 9.
  The GROUP BYClause The GROUP BY clause can be used 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 Using the GROUP BY Clause
• 10.
  The GROUP BYcolumn does not have to be in the SELECT list. SELECT AVG(salary) FROM employees GROUP BY department_id; The group function can be used in the ORDER BY clause.
• 11.
  Using the GROUPBY Clause on Multiple Columns
• 12.
  Illegal Queries UsingGroup Functions Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause.
• 13.
  Illegal Queries UsingGroup Functions  Cannot be used the WHERE clause to restrict groups.  We have to use the HAVING clause to restrict groups.  Cannot be used group functions in the WHERE clause.
• 14.
  The SELECT statementin the slide results in an error because it uses the WHERE clause to restrict the display of average salaries of those departments that have an average salary greater than $8,000. We can correct the slide error by using the HAVING clause to restrict groups.
• 15.
  Using the HAVINGClause (Another Example) The image in the slide displays the job ID and total monthly salary for each job with a total payroll exceeding $13,000. The example excludes sales representatives and sorts the list by the total monthly salary.
• 16.
  Nesting Group Functions Inthe image it displays the maximum average salary. Group functions can be nested to a depth of two. The example image in the slide displays the maximum average salary.
• 17.
  Thank You