CS457 Syllabus & Progress

Intro to Set Functions in SQL

• Set Functions

• Set Functions compute an aggregate value from a set of values

• The set functions available in SQL are:

 SUM( ): sums a set of values AVG( ): takes the average of a set of values MAX( ): finds the maximum value of a set of values MIN( ): finds the minimum value of a set of values COUNT( ): returns the number of elements in a set (duplicates are counted multiple times !!!) COUNT(DISTINCT ...): returns the number of distinct elements in a set ( duplicates are counted ONCE )

• Applying set function on a set of values

• Fact:

 SELECT returns a set of tuples

Therefore:

 We can therefore apply one or more set functions on the result (set) of the SELECT command.

• Syntax:

 ``` SELECT SetFunction( attribute ) , ... FROM .... WHERE .... ```

Meaning:

 Apply the set function SetFunction on the selected set of tuples returned by the SELECT command

• Example:

 ``` SELECT salary FROM employee Result (set): SALARY ---------- 30000 40000 25000 43000 38000 25000 25000 55000 Apply set functions on the result set: SELECT SUM(salary), AVG(salary), MAX(salary), MIN(salary), COUNT(salary), COUNT( DISTINCT salary ) FROM employee Result: SUM(SALARY) AVG(SALARY) MAX(SALARY) MIN(SALARY) COUNT(SALARY) COUNT(DISTINCT salary) ----------- ----------- ----------- ----------- ------------- ---------------------- 281000 35125 55000 25000 8 6 ```

• The query returns the total salary, the average salary, the max salary, the min salary and the number of salary values of all employees.

• Note:

 COUNT( ) will count duplicate values as multiple times To count duplicate value once, use COUNT(DISTINCT ...)

• The set of tuples used by Set Functions

• Important note:

 The output of a set function depends on the set of selected tuples output by the WHERE clase of the SELECT command

• Example:

 ``` select salary from employee where dno=4 salary --------- 25000.00 43000.00 <--- Max 25000.00 select max(salary) from employee where dno=4 MAX(SALARY) ----------- 43000.00 ```

• On the other hand:

 ``` select salary from employee where dno=5 salary --------- 31004.00 50000.00 <--- Max 38000.00 25000.00 select max(salary) from employee where dno=5 MAX(SALARY) ----------- 50000.00 ```

• Therefore:

 How well you can use set functions will depend very much on how well you can formulate the where clause that select the correct set of tuples !!!!

• Renaming the selected set function attribute

• Names given to set functions attributes:

 ``` select max(salary), avg(salary), sum(salary) from employee max(salary) avg(salary) sum(salary) ----------- ------------- ------------------------------- 55000.00 33875.000000 271000.00 ```

• You can rename the selected attributes with as alias clause inside the SELECT clause:

 ``` select max(salary) Max, avg(salary) Average, sum(salary) from employee Max Average sum(salary) --------- ------------- ------------------------------- 55000.00 33875.000000 271000.00 ```