AGGREGATE FUNCTION.pptx

Data base System-Aggregate function

1. Aggregate function

2. PREPARED BY Ms.G.ABIRAMI – III - B.Sc - Information Technology UNDER THE GUIDANCE OF Mrs.P.Anusha M.Sc (IT)., M.Phil.,D.P.T.T .,( Ph.D )., Assistant professor, Department of Information Technology, Bon secours college for women, Thanjavur.

3. SQL AGGREGATE FUNCTION SQL aggregation function is used to perform the calculation on multiple rows of a single column of a table. It returns a single value. It is also used to summarize the data . This function ignore the null values except the count function .

4. TYPES OF SQL AGGREGATION FUNCTION Count ( ) : Returns the numbers of rows Sum ( ) : Returns the sum Avg ( ) : Returns the average value Max ( ) : Returns the smallest value Min ( ) : Returns the largest value

5. COUNT FUNCTION Count function is used to count the number of rows in database table .It can work on both numeric and non-numeric data types . Count function uses the count (*) that returns the count of all the rows in a specific table. Count (*) considers duplicate and null . This function does not count the null value . Table name :Employee

6. EXAMPLE :table NAME DATE PAGES RAMU 22022-01-12 34 GEETHA 22022-01-12 74 SEETHA 22022-01-12 64 RAM 22022-01-12 94 SUKRIA 22022-01-12 64

7. syntax of count Syntax: select count(Pages) from employee; Count (pages) 5 1 row in set Syntax: select count (name) from employee; Count(name) 5 1 row in set

8. SUM FUNCTION Sum() function is an aggregate function that calculates the sum of all or distinct values in an expression . SYNTAX: SELECT SUM (PAGES) FROM EMPLOYEE; SUM(PAGES) 330 1 ROW IN A SET

9. Average function The avg function is used to calculate the average value of the numeric type. Average function returns the average of all non-null values. syntax: select avg (pages)from employee; avg(pages ) 66.0000 1row in set

10. Max function Max function is used to find the maximum value of certain column. This function determines the largest value of all selected values of columns. Syntax: select max (pages) from employee; Max(pages) 94 1 row in set

11. Min function The M in function is used to find the minimum value of certain column. This function determines the smallest value of all selected values of a column. syntax: Select min(pages)from employee ; Min(pages) 34 1 row in set