---

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