Aggregate Functions
Aggregate functions operate on a set of data and returns back a single value. Aggregate functions are used in SELECT clause, ORDER BY clause and HAVING clause.
Usually GROUP BY clause is used if a SELECT clause contains aggregate functions. If a SELECT clause contains aggregate functions, then columns listed in the SELECT clause must also be listed in the GROUP BY clause. It is permitted to use aggregate functions in the SELECT clause without a GROUP BY clause as long as the SELECT clause contains only aggregate functions.
Consider a Student table as shown below.
StudentID FirstName LastName Major GPA Tution
1 Mark Smith Music 3.50 20000
2 Jane Page History 3.20 10000
3 Tom Dennis Finance 3.80 50000
4 Matt Lee Biology 3.90 30000
5 Andrew Reid Physics 3.10 30000
6 Craig Brenton History 3.30 10000
7 Sana Smith Maths 3.90 40000
8 Mike Lewis Chemistry 3.10 30000
9 Jane Drake Biology 3.40 30000
10 Steve Thomas Physics 3.60 30000
Following are the most common aggregate functions:
AVG: Returns back the average value of an expression.
EXAMPLE
Query:
SELECT AVG(GPA) AS [Average GPA]
FROM Student
Output :
Average GPA
3.480000
COUNT: Returns back number of rows in the expression. If COUNT(*) is used, it returns back total number of rows in the result set, but if a column is used instead of an asterisk, ex. COUNT(ColumnName), then COUNT returns the number of rows where the data in the column is not null.
EXAMPLE
Query:
SELECT COUNT(*) AS [Total # Of Students]
FROM Student
Output :
Total # Of Students
10
MAX: Returns back the maximum of all the values in the expression.
EXAMPLE
Query:
SELECT MAX(GPA) AS [Maximum GPA]
FROM Student
Output :
Maximum GPA
3.9
MIN: Returns back minimum of all the values in the expression.
EXAMPLE
Query:
SELECT MIN(GPA) AS [Minimum GPA]
FROM Student
Output :
Minimum GPA
3.1
SUM: Returns back summation of all the values in the expression.
EXAMPLE
Query:
SELECT SUM(Tution) AS [Sum of Tution]
FROM Student
Output :
Sum of Tution
280000