Aggregate Functions

Key Concepts

Review core concepts you need to learn to master this subject

GROUP BY and ORDER BY with Numbers

MAX() Aggregate Function in SQL

SUM() Aggregate Function in SQL

COUNT() Aggregate Function in SQL

GROUP BY Clause in SQL

MIN() Aggregate Function in SQL

AVG() Aggregate Function in SQL

HAVING Clause in SQL

`GROUP BY` and `ORDER BY` with Numbers

SELECT COUNT(*) AS 'total_movies', 
   rating 
FROM movies 
GROUP BY 2 
ORDER BY 1;

The GROUP BY and ORDER BY clauses can reference the selected columns by number in which they appear in the SELECT statement. The query above will count the number of movies per rating, and will GROUP BY column 2 (rating) and ORDER BY column 1 (total_movies) in the SELECT statement.

`MAX()` Aggregate Function in SQL

SELECT COUNT(*) AS 'total_movies', 
   rating 
FROM movies 
GROUP BY 2 
ORDER BY 1;

The MAX() aggregate function in SQL returns the largest value in a column. For instance, to find the largest value of the amount column from the transactions table, the given query can be used.

`SUM()` Aggregate Function in SQL

SELECT COUNT(*) AS 'total_movies', 
   rating 
FROM movies 
GROUP BY 2 
ORDER BY 1;

The SUM() aggregate function in SQL returns the sum value of a column. For instance, to find the total disbursed salary, the above query can be used.

`COUNT()` Aggregate Function in SQL

SELECT COUNT(*) AS 'total_movies', 
   rating 
FROM movies 
GROUP BY 2 
ORDER BY 1;

The COUNT() aggregate function in SQL returns the total number of rows that match a specified criteria. For instance, to find the total number of employees who have more than 5 years of experience, the above query can be used. A column name of the table can also be used instead of *. Unlike COUNT(*), This variation COUNT(column_name) will not count NULL values for that column.

`GROUP BY` Clause in SQL

SELECT COUNT(*) AS 'total_movies', 
   rating 
FROM movies 
GROUP BY 2 
ORDER BY 1;

The 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 like records. The GROUP BY clause can come after FROM or WHERE but must come before any ORDER BY or LIMIT clause. The query above will count the number of movies per rating.

`MIN()` Aggregate Function in SQL

SELECT COUNT(*) AS 'total_movies', 
   rating 
FROM movies 
GROUP BY 2 
ORDER BY 1;

The 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.

`AVG()` Aggregate Function in SQL

SELECT COUNT(*) AS 'total_movies', 
   rating 
FROM movies 
GROUP BY 2 
ORDER BY 1;

The AVG() aggregate function in SQL returns the average value in a column. For instance, to find the average amount of salary for the employees having experience less than 5 years, the above query can be used.

`HAVING` Clause in SQL

SELECT COUNT(*) AS 'total_movies', 
   rating 
FROM movies 
GROUP BY 2 
ORDER BY 1;

The HAVING clause is used to further filter result set groups provided by the GROUP BY clause. HAVING is often used with aggregate functions to filter the result group based on an aggregate property. The query above will filter only years where more than 5 movies were released per year.

`ROUND()` Function in SQL

SELECT COUNT(*) AS 'total_movies', 
   rating 
FROM movies 
GROUP BY 2 
ORDER BY 1;

The 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 query above. This query will calculate the average rating of movies from 2015, rounding to 2 decimal places.