Aggregate functions in SQL
Aggregate functions:
Aggregate functions are the only functions in SQL which ignore the NULL values by default.
These are the built-in functions in SQL.
There are five types of aggregate functions in SQL which are listed below:
a.avg
b.sum
c.min and max
d.count
Aggregate functions:
*These functions by default ignores the NULL values.
*Assume emp table with fields(id,sal,age) and values as given below:
id sal age
1 15000 21
2 25000 25
3 30000 22
a.avg function:
This function returns the average.
syntax: avg(col_name)
ex:
select avg(id)as avgid
from emp
output:
avgid
2
b.sum function:
It returns the addition.
syntax: sum(col_name)
ex:
select sum(id) as sumid
from emp
output:
sumid
6
c.min and max functions:
It returns minimum and maximum value.
syntax: min(col_name)
max(col_name)
ex:
select min(sal)as minsal,max(sal) as maxsal
from emp
output:
minsal maxsal
15000 30000
d.count function:
It returns the no.of rows.
syntax: count(col_name)
ex:
select count(age)as countage
from emp
output:
countage
3
SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:
• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum
SELECT AVG(TransQty) avgTransQty
FROM IntTrans
SELECT COUNT(TransQty) COUNTTransQty
FROM IntTrans
SELECT MAX(TransQty) MAXTransQty
FROM IntTrans
SELECT MIN(TransQty) MINTransQty
FROM IntTrans
SELECT SUM(TransQty) SUMTransQty
FROM IntTrans
SELECT COUNT(*) COUNTTransQty
FROM IntTrans