Set functions available in
SQL
- 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)
|
|
Using
set functions in the
SELECT clause
- Syntax
SELECT SetFunction( attribute ) , ...
FROM ....
WHERE ....
|
Meaning:
- Apply
the set function
SetFunction( )
on the
attribute values
in the
tuples
selected
by the
WHERE clause
|
- Note:
- To apply the
set function on a
different set of
tuples:
- Change the
WHERE clause
condition
|
|
|
Example 1:
set functions in the
SELECT clause
- This query
finds
the salary of
all employees
in the company:
SELECT salary SALARY
FROM employee ----------
30000.00
40000.00
25000.00
43000.00
38000.00
25000.00
25000.00
55000.00
|
- Then the
sum
set function
will compute the
sum of
all employees'
salary:
SELECT sum(salary) SUM(SALARY)
FROM employee -----------
281000.00
|
|
Example 2:
set functions in the
SELECT clause
- This query
finds
the salary of
all employees
in
department #4:
SELECT salary SALARY
FROM employee ----------
WHERE dno = 4 25000.00
43000.00
25000.00
|
- Then the
sum
set function
will compute the
sum of the
salary of
employees in
department #4:
SELECT sum(salary) SUM(SALARY)
FROM employee -----------
WHERE dno = 4 93000.00
|
|
Renaming
the output attributes
with an alias
- Names given to
set functions
attributes are
not
legal
identifiers:
select max(salary), avg(salary), sum(salary)
from employee
max(salary) avg(salary) sum(salary) <--- Illegal identifiers
----------- ------------- ---------
55000.00 33875.000000 271000.00
|
- To obtain
names that are
identifiers,
you can rename the
attributes:
select max(salary) Max, avg(salary) Average, sum(salary) Sum
from employee
Max Average Sum <--- Legal identifiers
--------- ------------- ---------
55000.00 33875.000000 271000.00
|
- This feature will be
important when
set functions are used
in
subqueries
|
❮
❯