Aggregate functions

August 20, 2019

Aggregate functions in DBMS 

  • SQL provides a number of built-in functions to perform operations on data these functions are very much useful for performing mathematical calculations on table data
  • Aggregate functions return a single value after performing calculations on a set of values, here will discuss the five frequently used aggregate functions provided by SQL
  • These aggregate functions are used with the SELECT statement at a time only one column can be applied with the function

General syntax 

SELECT functionname(column_name)
FROM table_name
Aggregate Functions

consider sample table emp

avg() 

This function returns the Arithmetic mean of all the values present in that column

eidnameagesalary
65Trish229000
66Rishi298000
67Mahi346000
68Mani4410000
69Puppy358000

SQL query to find average salary 

SELECT avg(salary) from Emp;
avg(salary)
8200

count() 

It returns the number of rows present in the table which can be either based upon a condition or without a condition

SQL query to count employees, satisfying specified condition is,

SELECT COUNT(name) FROM Emp WHERE salary = 8000;

O/P

count(name)
2

Example of COUNT(distinct) 

Consider the following Emp table

eidnameagesalary
65Trish229000
66Rishi298000
67Mahi346000
68Mani4410000
69Puppy358000

SQL query is,

SELECT COUNT(DISTINCT salary) FROM emp;

O/P

count(distinct salary)
4

max() 

It returns the maximum i.e the largest value among all the values present in that column

SQL query to find the Maximum salary 

SELECT MAX(salary) FROM emp;

O/P

MAX(salary)
10000

min() 

It returns the minimum value i.e the smallest numerical value among all the values present in that particular column

SQL query to find minimum salary

SELECT MIN(salary) FROM emp;

Result will be,

MIN(salary)
6000

sum() 

The sum function returns the arithmetic sum of all the values present in that column

SQL query to find sum of salaries 

SELECT SUM(salary) FROM emp;

Result of above query is,

SUM(salary)
41000

Find the 2nd highest salary of an employee(interview question)

  • This is the most commonly asked interview question. Hence I recommend you remember this example
  • This solution uses a sub query to first exclude the maximum salary from the data set and then again finds the maximum salary, which is effectively the second maximum salary from the Employee table.
select max(salary) from employee where salary < ( select max(salary) from emp);

0/P

MAX(salary)
9000

How it works

  • Here first we are creating a result set excluding first highest salary i.e max(salary )
  • Again applying max(sal) on this result would Fetch the second highest salary obviously

Note  

Whenever we are using aggregate functions in a select statement you are not allowed to use other columns

SELECT avg(salary),eid from Emp;//error

In a select statement you can use only one aggregate function at a time