SQL Aggregate Functions :
In my previous article i have explained the different SQL functions with examples. In this article i will focus on SQL Aggregate functions with different real life examples.SQL aggregate functions are most used sql functions in industry example.
“SQL Functions are nothing but the system written programs to perform the calculation of the data or to process the strings,numbers e.t.c”
SQL Aggregate functions with real life examples:
In this section i will give you SQL aggregate functions with its explanation. I will try to give you the different kind of real industry examples of SQL Aggregate functions. Aggregate functions are functions which has multiple inputs but it gives the aggregated result for multiple rows of the table. The SQL Aggregate functions are mainly used to perform calculations on single column.These functions used mostly in real world industry examples.In following section i will give you syntax of aggregate function and real life use of aggregate function with explaining the example.
This will definitely helps the user to understand the specific functionality of aggregate functions in SQL.The aggregate functions gives us the summarized result for particular column. These aggregate functions are mostly uses the group by clause internally with select statement.
1.Min Aggregate Function :
The Min aggregate function stands for the minimum. It returns the minimum value by checking the whole column.To calculate the minimum value in the column of the table the column must be in number datatype format.
Syntax :
min(number_column)
Select Min(number_column) from tablename;
Select Min(Number-column) from tablename where condition;
These are above different syntax of Min function. Let say you need to find out the minimum grade for the Employees for specific course from Employee table.
Select Min(grade) from Employee;
The above query will give you the minimum grade for Employees.Make sure that the grade column is in numeric format.If you want to find out the Min grade for the Department name Security then following query is helpful.
Select Min(grade)from Employee where department=’Security’;
The above query will give you the minimum grade from Employee table where department name is security.
2. Max Aggregate Function :
The Max aggregate function stands for the Maximum. It returns the maximum value by checking the whole column.To calculate the maximum value in the column of the table the column must be in number datatype format.
Syntax :
max(number_column)
Select Max(number_column) from tablename;
Select Max(Number-column) from tablename where condition;
These are above different syntax of Max function. Let say you need to find out the maximum grade for the Employees for specific course from Employee table.
Select Max(grade) from Employee;
The above query will give you the maximum grade for Employees.Make sure that the grade column is in numeric format.If you want to find out the Max grade for the Department name Security then following query is helpful.
Select Max(grade)from Employee where department=’Security’;
The above query will give you the maximum grade from Employee table where department name is security.
3. Avg Aggregate Function :
The Avg aggregate function is one of the most used SQL Aggregate function.This function provides you the Average of the given column.Calculating average value of given column is useful in different purpose. The calculating average of specific column is used in different reports as well.The Avg is abbreviation of Average.The column must be in number format to calculate its Average.
Syntax :
Avg(number_column)
Select Avg(number_column) from tablename;
Select Avg(Number-column) from tablename where condition;
These are above different syntax of avg function. Lets say user needs to calculate the average salary of the employee. This expression is most used expression in HR reporting.
Select Avg(salary) from Employee;
The above expression is used to calculate the Average salary of the employee from Employees table.
There are some scenarios where user needs to calculate the average for specific departments.Lets say user want to calculate the average salary of employees for Business Intelligence department.
Select Avg(salary) from Employee where department=’Business Intelligence’;
The above statement will calculate the average salary for employees where department is Business Intelligence.
If user wants to calculate the salary of employees where department is Business intelligence with grouping of different positions like programmers,senior programmers.Here the group by expression is used with the query.
Select Avg(salary) from Employee where department=’Business Intelligence’ group by position;
These are some most important examples of Avg function in SQL which is one of the most used SQL aggregate function.
4.Count Aggregate function:
This function is undoubtedly most used aggregate function.The count function is used to calculate the count of the rows in the specified column. There are so many scenarios where user needs to calculate the count of the rows.The Count function is most used function in reporting and analytics.
Syntax :
A) COUNT (Numeric_Column_Name)
B) COUNT (*)
C) COUNT (DISTINCT Column_Name)
Example 1: Calculate the count of Employees from Employee table.
Select count(*) from Employees;
The above query is used to calculate all the rows from Employee table.It calculates the total number of records in Employees table. If there are null values in Employees table it calculates that also.
Example 2 : Calculate the count of Employees who are software Engineers
Select count(Eno) from Employees where job_title=’Software Engineer’;
The above query is used to calculate the number of employees whos job title is Software Engineer.
Example 3 : Calculate the count of Employees who are Software Engineer by removing duplicate Employees.
This is most common scenario where user needs to remove duplicates. If the Employee table does not have any constraints then the values from Employee table gets duplicated.In that case user needs to use distinct keyword before counting the column name.
Select count(distinct Eno) from Employees where job_title=’Software Engineer’;
The above statement will fetch the distinct employees whose job_title is ‘Software Engineer’.
5.Sum Aggregate Function :
There are lot of times user will confused in Avg function and Sum function.The Sum function calculates the sum of the numeric column.
Syntax:
Sum(number_column)
Select Sum(number_column) from tablename;
Select Sum(Number-column) from tablename where condition;
Lets say user wants to calculate the total salary from Employees table.
Select Sum(salary) from Employees;
The above statement will give you the total salary of Employees.
There are scenarios where user wants to calculate the sum of the salary departmentwise.
Select Sum(salary) from Employees where deparment_name=’Software’;
The above command will calculate the Sum of salary for Department name as ‘Software’.
These are some most important examples of SQL aggregate functions. I hope you like this article on SQL aggregate functions.If you like this article or if you have any issues with the same kindly comment in to comment section.