Aggregate Functions

Aggregate Functions

Selects total group and compares it.

Table EMPLOYEES

ENO

ENAME

DES

DATE

SAL

1

MANOJ

MANAGER

01-01-2007

26000

2

SEENA

ACCOUNTANT

02-03-2007

12000

3

JINEESH

PROGRAMMER

05-25-2007

24000

4

GEORGE THOMAS

GENERAM MANAGER

03-24-2007

40000

5

ANU THOMAS

ACCOUNTANT

07-12-2007

10000

6

PADMA

CLEARK

09-23-2007

8000

7

PETER

CLEARK

10-25-2007

7500

· SELECT MIN (SAL) FROM EMPLOYEES

· SELECT MAX (SAL) FROM EMPLOYEES

· SELECT SUM (SAL) FROM EMPLOYEES

· SELECT AVG (SAL) FROM EMPLOYEES

Count()

Counts the Number of Records in a Column

· SELECT COUNT (ENAME) FROM EMPLOYEES

· SELECT COUNT (*) FROM EMPLOYEES

Group by

Groups the record of a table

Note: For this - modify the table

add a new column named DEPTNO (use ALTER query )

ALTER TABLE EMPLOYEES COLUMN EDPTNO INT

and update all row (use UPDATE query)

UPDATE EMPLOYEES SET DEPTNO=20 WHERE ENAME='MANOJ'

Now the structure changes

ENO

ENAME

DES

DATE

SAL

DEPTNO

1

MANOJ

MANAGER

01-01-2007

26000

20

2

SEENA

ACCOUNTANT

02-03-2007

12000

20

3

JINEESH

PROGRAMMER

05-25-2007

24000

30

4

GEORGE THOMAS

GENERAM MANAGER

03-24-2007

40000

40

5

ANU THOMAS

ACCOUNTANT

07-12-2007

10000

10

6

PADMA

CLEARK

09-23-2007

8000

20

7

PETER

CLEARK

10-25-2007

7500

10



· SELECT DEPTNO, COUNT (*) FROM EMPLOYEES GROUP BY DEPTNO


Having

To give a condition for a group by statement

· SELECT DEPTNO, COUNT(*) FROM EMPLOYEES GROUP BY DEPTNO HAVING COUNT (*)>3