Aggregate Functions In SQL

 

Aggregate Functions In SQL:

Aggregate Functions are used to manipulate the data in the database. They can generate the summary value of the given column in the WHERE Clause. These functions generate a single value from the given column of a table. There are different types of aggregate functions some can be applied on numeric and non_numeric fields such as MAX(), MIN() and COUNT() functions, however, some functions can only be implemented on numeric data such as SUM() and AVG(). We can use the DITINCT keyword before the column name if we want to exclude the duplicate rows. However, this keyword doesn’t have any effect on MIN() and MAX() function Although it can affect the result of SUM() and AVG() function. We can use DISTINCT KEYWORD to include the computation of duplicate rows in the result of SUM() and AVG(). But there is a restriction about the use of DISTINCT that it can only be used once in a query. There must be the criteria while using the aggregate functions. Criteria can be specified through WHERE Clause or in some cases GROUP BY Clause otherwise the query will be illegal. Now we will discuss the functionality of each aggregate function with the help of an example.

COUNT() Function:

COUNT() is the function of SQL that is used to count the number of rows in the specified column of WHERE Clause. There are three forms of COUNT() functions. These are

• COUNT(*)
• COUNT(Column_Name)
• COUNT(DISTINCT Column_Name)

COUNT(*):

It is used to count all the rows of the specified column. When each function performs functionality then it eliminates NULL values and performs operation only non_NULL values But COUNT(*) is the only function that doesn’t eliminate the NULL values and also count those rows that contain NULL values. Moreover, it also counts duplicate rows.

Example:

Let suppose there is a “Student” Table.

Now if we want to know that how many students are enrolled in the subject “CS” then  the query will be:

Result:

 As 5 students are studying CS subject in above table

 COUNT(Column_Name):

It is used to count the number of rows with respect to a specific column excluding rows containing NULL values.

Example:

If we want to count the total rows that exist in the Marks column. As there are 8 students but one student has the NULL value of Marks, the query will be :

 

Result:

 

COUNT(DISTINCT Column_Name):

It is used to count the number of rows according to the specified column in the criteria of WHERE Clause excluding duplicate rows.

Example:

If we want to know how many subjects are taught to the students, therefore the query will be:

Result:

 

SUM () Function:

This function returns the sum of all values of a specified column in an expression. The DISTINCT keyword is supported by this function to summarize the unique values of an expression. This function can only be applied to numeric columns and ignores NULL values by default.

Example:

If we want to know the total marks of “john” in different subjects, then we can apply this query.

 

Result:

AVG () Function:

AVG () is used to find out the average of the values of the given expression. The expression can be the column name to which average we want to find out. This function automatically excludes the Null values and can be applied only to those columns that contain numeric values.

Example:

If we want to find out the average marks of a student named “john” as we can see in the above table john has multiple subjects so the query will be:

 

Result:

 

MAX () And MIN () Function:

MAX () And MIN () Functions are used to find out the maximum and minimum value in a given column e.g we can get to know about the maximum and minimum salaries of employees through these functions. These functions also ignore NULL values and can be used with all types of data.

Example:

If we want to know the maximum and minimum marks of subject 'CS' ,the query will be:

Result:

So aggregate functions are used to perform some specific functionalities and  Aggregate functions can only be used in the SELECT statement and HAVING Clause and can't be applied elsewhere.

To know more detail clauses of SQL visit

SQL Clauses