SQL Aggregate Functions

Filed Under: SQL

SQL Aggregate Functions basically operate on multiple columns to perform the operations and serve to represent the output as an entity representing the operation executed.

SQL Aggregate Functions
SQL Aggregate Functions

Syntax:

aggregate_function (DISTINCT | ALL expression)
  • DISTINCT enables the user to select distinct values from the table i.e. if multiple attributes contain the same value, then only single distinct value is considered for the calculations.
  • ALL makes sure that even the repeated values are considered for the calculations.

Aggregate Functions in SQL:

  • AVG()
  • MIN()
  • MAX()
  • SUM()
  • COUNT()

1. SQL AVG() function

SQL AVG() function returns the average of all the selected values from the corresponding column of the table.

Let’s us consider the following table to understand the Aggregate functions:

Table Name: Details

IDNameAmountAge
1Safa500021
2Aman250023
3Rehaan2000025
4Seema1200025

Example:

select AVG(Amount) from Details;

Output:

9875

2. SQL MIN() function

SQL MIN() function returns the minimum value of all the selected values from the corresponding column of the table.

Example:

select MIN(Amount) from Details;

Output:

2500

3. SQL MAX() function

SQL MAX() function returns the maximum value of all the selected values from the corresponding column of the table.

select MAX(Amount) from Details;

Output:

20000

4. SQL SUM() function

SQL SUM() function returns the summation of all the selected values from the corresponding column of the table.

Example 1: Basic Understanding of SUM() Function

select SUM(Amount) from Details;

Output:

39500

Example 2: SQL SUM() with GROUP BY Clause

SQL SUM() can be framed together with SQL GROUP BY Clause to represent the output results by a particular label/values.

SELECT SUM(Amount),Name  
FROM Details 
WHERE Age>21 
GROUP BY Name;  

Output:

SQL SUM() with GROUP BY
SUM() with GROUP BY

Example 3: SQL SUM() with HAVING Clause

SQL SUM() function can be used along with SQL HAVING Clause; HAVING Clause is basically used to specify the condition to be operated on the set of values in the table.

SELECT SUM(Amount),Name  
FROM Details 
GROUP BY Name
HAVING SUM(Amount)>2500;

Output:

SQL SUM() with HAVING Clause
SUM() with HAVING Clause

5. SQL COUNT() function

Example 1: COUNT() function to return the count of a particular column of a table

select COUNT(Amount) from Details;

Output:

4

Example 2: COUNT(*) function

This function returns the count of all the values present in the set of records of a table.

SELECT count(*) from Details;

Output:

4

Example 3: COUNT() with WHERE Clause

SELECT count(*) from Details
where Age<25;

Output:

2

Example 4: COUNT() with GROUP BY Clause

SELECT count(Amount),Name from Details
where Age<25
Group by Name;

Output:

SQL COUNT() with GROUP BY Clause
COUNT() with GROUP BY Clause

Example 5: COUNT() with HAVING Clause

SELECT count(Amount),Age, Name from Details
Group by Name
HAVING Age>=25;

Output:

SQL COUNT() with HAVING Clause
COUNT() with HAVING Clause

Conclusion

Thus, in this article, we have understood different SQL Aggregate Functions.


Reference

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages