6. Aggregate Functions.pdf

Aggregate Functions

1. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 1

6. Aggregate Functions
Aggregate Function
Aggregate functions are functions that take a collection (a set or multiset) of values as input and return a
single value. SQL offers five built-in aggregate functions:
 Average: avg
 Minimum: min
 Maximum: max
 Total: sum
 Count: count
The input to sum and avg must be a collection of numbers, but the other operators can operate on collections
of nonnumeric data types, such as strings, as well.

Find the average salary of instructors in the Computer Science department.

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein Physics 95000
32343 El Said History 60000
33456 Gold Physics 87000
45565 Katz Comp.Sci. 75000
58583 Califieri History 62000
76543 Singh Finance 80000
76766 Crick Biology 72000
83821 Brandt Comp.Sci. 92000

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
98345 Kim Elec.Eng. 80000

12 rows selected.

SQL> select avg (salary)
2 from instructor
3 where dept_name= 'Comp.Sci.';

AVG(SALARY)
-----------

2. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 2

77333.3333

Give a meaningful name to the attribute by using the as clause

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein Physics 95000
32343 El Said History 60000
33456 Gold Physics 87000
45565 Katz Comp.Sci. 75000
58583 Califieri History 62000
76543 Singh Finance 80000
76766 Crick Biology 72000
83821 Brandt Comp.Sci. 92000

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
98345 Kim Elec.Eng. 80000

12 rows selected.

SQL> select avg (salary) as avg_salary
2 from instructor
3 where dept_name='Comp.Sci.';

AVG_SALARY
----------
77333.3333

Find the total number of instructors who teach a course in the spring 2010 semester

SQL> select * from teaches;

ID COURSE_I SEC_ID SEMEST YEAR
----- -------- -------- ------ ----------
10101 CS-101 1 Fall 2009
10101 CS-315 1 Spring 2010
10101 CS-347 1 Fall 2009
12121 FIN-201 1 Spring 2010

3. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 3

15151 MU-199 1 Spring 2010
22222 PHY-101 1 Fall 2009
32343 HIS-351 1 Spring 2010
45565 CS-101 1 Spring 2010
45565 CS-319 1 Spring 2010
76766 BIO-101 1 Summer 2009
76766 BIO-301 1 Summer 2010

ID COURSE_I SEC_ID SEMEST YEAR
----- -------- -------- ------ ----------
83821 CS-190 1 Spring 2009
83821 CS-190 2 Spring 2009
83821 CS-319 2 Spring 2010
98345 EE-181 1 Spring 2009

15 rows selected.

SQL> select count (distinct ID)
2 from teaches
3 where semester = 'Spring' and year = 2010;

COUNT(DISTINCTID)
-----------------
6

Find the number of tuples in the course relation

SQL> select * from course;

COURSE_ TITLE DEPT_NAME
------- -------------------------------------------------- --------------------
CREDITS
----------
BIO-101 Intro. to Biology Biology
4

BIO-301 Genetics Biology
4

BIO-399 Computational Biology Biology
3

COURSE_ TITLE DEPT_NAME
------- -------------------------------------------------- --------------------

4. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 4

CREDITS
----------
CS-101 Intro. to Computer Science Comp.Sci.
4

CS-190 Game Design Comp.Sci.
4

CS-315 Robotics Comp.Sci.
3

COURSE_ TITLE DEPT_NAME
------- -------------------------------------------------- --------------------
CREDITS
----------
CS-319 Image Processing Comp.Sci.
3

CS-347 Database System Concepts Comp.Sci.
3

EE-181 Intro. to Digital Systems Elec.Eng.
3

COURSE_ TITLE DEPT_NAME
------- -------------------------------------------------- --------------------
CREDITS
----------
FIN-201 Investment Banking Finance
3

HIS-351 World History History
3

MU-199 Music Video Production Music
3

COURSE_ TITLE DEPT_NAME
------- -------------------------------------------------- --------------------
CREDITS
----------
PHY-101 Physical Principles Physics
4

5. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 5


13 rows selected.

SQL> select count (*)
2 from course;

COUNT(*)
----------
13

6. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 6

GROUP BY Clause
There are circumstances where we would like to apply the aggregate function not only to a single set of
tuples, but also to a group of sets of tuples; we specify this wish in SQL using the group by clause. The
attribute or attributes given in the group by clause are used to form groups. Tuples with the same value on
all attributes in the group by clause are placed in one group.

Find the average salary in each department.

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein Physics 95000
32343 El Said History 60000
33456 Gold Physics 87000
45565 Katz Comp.Sci. 75000
58583 Califieri History 62000
76543 Singh Finance 80000
76766 Crick Biology 72000
83821 Brandt Comp.Sci. 92000

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
98345 Kim Elec.Eng. 80000

12 rows selected.

SQL> select dept_name, avg (salary) as avg_salary
2 from instructor
3 group by dept_name;

DEPT_NAME AVG_SALARY
-------------------- ----------
Physics 91000
Finance 85000
Elec.Eng. 80000
Comp.Sci. 77333.3333
Biology 72000
Music 40000
History 61000

7. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 7

7 rows selected.

Find the number of instructors in each department who teach a course in the spring 2010 semester.

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein Physics 95000
32343 El Said History 60000
33456 Gold Physics 87000
45565 Katz Comp.Sci. 75000
58583 Califieri History 62000
76543 Singh Finance 80000
76766 Crick Biology 72000
83821 Brandt Comp.Sci. 92000

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
98345 Kim Elec.Eng. 80000

12 rows selected.

SQL> select * from teaches;

ID COURSE_I SEC_ID SEMEST YEAR
----- -------- -------- ------ ----------
10101 CS-101 1 Fall 2009
10101 CS-315 1 Spring 2010
10101 CS-347 1 Fall 2009
12121 FIN-201 1 Spring 2010
15151 MU-199 1 Spring 2010
22222 PHY-101 1 Fall 2009
32343 HIS-351 1 Spring 2010
45565 CS-101 1 Spring 2010
45565 CS-319 1 Spring 2010
76766 BIO-101 1 Summer 2009
76766 BIO-301 1 Summer 2010

ID COURSE_I SEC_ID SEMEST YEAR
----- -------- -------- ------ ----------

8. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 8

83821 CS-190 1 Spring 2009
83821 CS-190 2 Spring 2009
83821 CS-319 2 Spring 2010
98345 EE-181 1 Spring 2009

15 rows selected.

SQL> select dept_name, id from instructor natural join teaches;

DEPT_NAME ID
-------------------- -----
Comp.Sci. 10101
Comp.Sci. 10101
Comp.Sci. 10101
Finance 12121
Music 15151
Physics 22222
History 32343
Comp.Sci. 45565
Comp.Sci. 45565
Biology 76766
Biology 76766

DEPT_NAME ID
-------------------- -----
Comp.Sci. 83821
Comp.Sci. 83821
Comp.Sci. 83821
Elec.Eng. 98345

15 rows selected.

SQL> select dept_name, count (distinct ID) as instr_count
2 from instructor natural join teaches
3 where semester = 'Spring' and year = 2010
4 group by dept_name;

DEPT_NAME INSTR_COUNT
-------------------- -----------
Comp.Sci. 3
Finance 1
History 1
Music 1

9. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 9

Each instructor in a particular group (defined by dept name) can have a different ID, and since only
one tuple is output for each group, there is no unique way of choosing which ID value to output.

SQL> select dept_name, ID, avg (salary)
2 from instructor
3 group by dept_name;
select dept_name, ID, avg (salary)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression


SQL> select dept_name, avg (salary)
2 from instructor
3 group by dept_name;

DEPT_NAME AVG(SALARY)
-------------------- -----------
Physics 91000
Finance 85000
Elec.Eng. 80000
Comp.Sci. 77333.3333
Biology 72000
Music 40000
History 61000

7 rows selected.

10. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 10

HAVING Clause
At times, it is useful to state a condition that applies to groups rather than to tuples. For example, we might
be interested in only those departments where the average salary of the instructors is more than $42,000.
This condition does not apply to a single tuple; rather, it applies to each group constructed by the group by
clause. To express such a query, we use the having clause of SQL. SQL applies predicates in the having
clause after groups have been formed, so aggregate functions may be used.

Find the departments where the average salary of the instructors is more than $42,000

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein Physics 95000
32343 El Said History 60000
33456 Gold Physics 87000
45565 Katz Comp.Sci. 75000
58583 Califieri History 62000
76543 Singh Finance 80000
76766 Crick Biology 72000
83821 Brandt Comp.Sci. 92000

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
98345 Kim Elec.Eng. 80000

12 rows selected.


SQL> select dept_name, avg (salary) as avg_salary
2 from instructor
3 group by dept_name
4 having avg (salary) > 42000;

DEPT_NAME AVG_SALARY
-------------------- ----------
Physics 91000
Finance 85000
Elec.Eng. 80000
Comp.Sci. 77333.3333
Biology 72000

11. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 11

History 61000

6 rows selected.

12. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 12

References:
 Database system concepts by Abraham Silberschatz, Henry F. Korth, S. Sudarshan (McGraw Hill
International Edition) sixth edition.
 Database system concepts by Abraham Silberschatz, Henry F. Korth, S. Sudarshan (McGraw Hill
International Edition) fifth edition.
 http://codex.cs.yale.edu/avi/db-book/db4/slide-dir/
 http://codex.cs.yale.edu/avi/db-book/db5/slide-dir/
 http://codex.cs.yale.edu/avi/db-book/db6/slide-dir/