Aggregate Functions in SQL
Aggregate functions perform a calculation on a set of values and return a single value:
COUNT() Aggregate Function
COUNT() aggregate function in SQL returns the total number of rows that match the specified criteria. For instance, to find the total number of employees who have less than 5 years of experience, the given query can be used.
Note: A column name of the table can also be used instead of
COUNT(*), this variation
COUNT(column) will not count
NULL values in that column.
SUM() Aggregate Function
SUM() aggregate function takes the name of a column as an argument and returns the sum of all the value in that column.
AVG() Aggregate Function
AVG() aggregate function returns the average value in a column. For instance, to find the average
salary for the employees who have less than 5 years of experience, the given query can be used.
ROUND() function will round a number value to a specified number of places. It takes two arguments: a number, and a number of decimal places. It can be combined with other aggregate functions, as shown in the given query. This query will calculate the average rating of movies from 2015, rounding to 2 decimal places.
GROUP BY Clause
GROUP BY clause will group records in a result set by identical values in one or more columns. It is often used in combination with aggregate functions to query information of similar records. The
GROUP BY clause can come after
WHERE but must come before any
ORDER BY or
The given query will count the number of movies per rating.
GROUP BY and
ORDER BY clauses can reference the selected columns by number in which they appear in the
SELECT statement. The example query will count the number of movies per rating, and will:
HAVING clause is used to further filter the result set groups provided by the
GROUP BY clause.
HAVING is often used with aggregate functions to filter the result set groups based on an aggregate property. The given query will select only the records (rows) from only years where more than 5 movies were released per year.
MAX() Aggregate Function
MAX() aggregate function in SQL takes the name of a column as an argument and returns the largest value in a column. The given query will return the largest value from the
MIN() Aggregate Function
MIN() aggregate function in SQL returns the smallest value in a column. For instance, to find the smallest value of the
amount column from the table named
transactions, the given query can be used.