1 / 19

Basic Group Functions (without GROUP BY clause)

Basic Group Functions (without GROUP BY clause). Week 5 – Chapter 5. Objectives. Identify group functions Describe how group functions (without a GROUP BY clause) are used. Group Function Illustration. Group functions operate on sets of rows to give a one row result per group, eg:.

zoltin
Download Presentation

Basic Group Functions (without GROUP BY clause)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Basic Group Functions (without GROUP BY clause) Week 5 – Chapter 5

  2. Objectives • Identify group functions • Describe how group functions (without a GROUP BY clause) are used

  3. Group Function Illustration Group functions operate on sets of rows to give a one row result per group, eg: SELECT MAX(salary) MAXSAL FROM employees; EMPLOYEES ID SALARY 100 24000 101 17000 102 17000 103 9000 … 178 7000 200 4400 201 13000 202 6000 205 12000 206 8300 maximum salary in the EMPLOYEES table MAXSAL 24000

  4. Group Functions • COUNT(column) – counts the number of rows with a non-null value for specified column • SUM(column) – sums all values for column • AVG(column) – averages all non-null values for column • MIN(column) – finds minimum of values for column • MAX(column) – finds maximum of values for column • STDDEV(column) – provides standard deviation of column values • VARIANCE(column) – provides variance of column values

  5. Syntax of Group Functions The general syntax for a SELECT statement with a group function and no GROUP BY clause is: SELECT group_function(expression), group_function(expression), … FROM table [WHERE condition]; • A SELECT statement with a group function(s) and no GROUP BY clause returns only one row as its result • When functions are used in SELECT clause use of an alias is recommended so heading will be meaningful eg SELECT COUNT(*) AS NUMEMPS FROM employees

  6. COUNT Function • COUNT counts the number of rows containing a value in the specified column and returns a single value • A specific column may be provided as the argument or an * is used to represent all columns • The values returned by COUNT(*) and COUNT(column) can differ if nulls are permitted in the column (There can never be a row in a table containing only NULLS since every row in a table must have a value for the Primary Key.)

  7. COUNT Function Examples • Example 1: How many employees are there in the company? SELECT COUNT(*) NUMEMPS • FROM employees; NUMEMPS 20 • Example 2: How many employees are in department 20? SELECT COUNT(*) NUMDEPT20 FROM employees WHERE department_id = 20; NUMDEPT20 2 • Example 3: How many employees are in depts? SELECT COUNT(department_id) DEP FROM employees; DEP 19

  8. COUNT Function Examples(ctd) • Example 4: How many employees are in the company and how many employees are paid on commission? (NULL values are not included in the COUNT of a column!) SELECT COUNT(*) NUMEMPS, COUNT(commission_pct) NUMCOMM FROM employees NUMEMPS NUMCOMM 20 4

  9. COUNT Function Examples(ctd) • Example 5: How many employees are in departments and how many departments are these employees assigned to? (Shows use of DISTINCT with COUNT to count number of distinct values). Default of COUNT is to consider all values including duplicates; use of DISTINCT makes the function count duplicated values only once. SELECT COUNT(department_id) AS "# EMPS IN DEPTS", COUNT(DISTINCT department_id) AS "# DEPTS with EMPS" FROM employees; # EMPS IN DEPTS # DEPTS with EMPS 19 7

  10. AVG Function • AVG function will work only on numeric columns such as SAL. • The average value of the column for a group of 0 or more rows is calculated and a single value returned (i.e. total of all non-null values for the column in rows specified divided by the number of non-null values found) • NULL values are ignored by AVG function • To have NULLs considered in the calculation of the average you must also use the NVL function

  11. AVG Function Examples SELECT AVG(salary) AVERAGE FROM employees WHERE department_id = 80; Example 1: Display the average salary paid to employees AVERAGE 10033.3333 NOTE: When AVG is used, the value returned should normally be rounded or truncated SELECT ROUND(AVG(salary),2) AVERAGE FROM employees WHERE department_id = 80; AVERAGE 10033.33

  12. AVG Function Examples (ctd) Example 2: Display the average salary and average commission for all employees paid on commission SELECT COUNT(*) NUMCOMM, ROUND(AVG(salary),2) AVGSAL, ROUND(AVG(commission_pct),2) AVGCOMM FROM employees WHERE commission_pct IS NOT NULL; NUMCOMM AVGSAL AVGCOMM 4 9275 .21

  13. AVG Function Examples (ctd) Example 3: Display the average salary and average commission for all employees (note: NULLL values may occur in commission_pct column so convert to 0 for calculations) SELECT COUNT(*) NUMCOMM, ROUND(AVG(salary),2) AVGSAL, ROUND(AVG(NVL(commission_pct,0)),2) AVGCOMM FROM employees; NUMCOMM AVGSAL AVGCOMM 20 8775 .04

  14. SUM Function • SUM works on numeric values such as Salary. • The total of the values in the column is calculated and this single value is returned. • Example: what is the total of all salaries paid SELECT SUM (salary) TOTSAL FROM employees; TOTSAL 175500

  15. MIN and MAX Functions • MIN returns the minimum value found in the column (NULL does not count as a value unless the NVL function is used) • MAX returns maximum value found in column • MIN and MAX will work on columns of any data-type • Example 1: What are the lowest and highest salaries? SELECT MIN(salary) MINSAL, MAX(salary) MAXSAL FROM employees; MINSAL MAXSAL 250024000

  16. MIN and MAX Functions (ctd) Example 2: What are the earliest and latest hire dates for employees? SELECT MIN(hire_date) AS EARLIEST, MAX(hire_date) AS LATEST FROM employees; EARLIEST LATEST 17-JUN-87 29-JAN-00

  17. MIN and MAX Functions (ctd) Example 2: What are alphabetically the first and last values of last name? SELECT MIN(last_name) AS FIRST, MAX(last_name) AS LAST FROM employees; FIRST LAST Davies Zlotkey

  18. Important Note on Group Functions • If you include a group function in a SELECT clause, you cannot display values from individual rows as well (unless the individual column appears in the GROUP BY clause as will be seen next week). • You will receive an error message if you include a group function and a non-group expression referencing a column in a SELECT clause

  19. Group Function Error • Example: SELECT department_id, AVG(salary) FROM employees; • ERROR at line 1: ORA-00937: not a single-group group function • Query does not make sense: there is only one value of average salary to display and there will be 14 values of dept_no to display (one for each row of the employees table)

More Related