# Aggregate Functions

Learn powerful functions for performing complex database operations with ease.

Start## Key Concepts

Review core concepts you need to learn to master this subject

Column References

`SUM()`

Aggregate Function

`MAX()`

Aggregate Function

`COUNT()`

Aggregate Function

`GROUP BY`

Clause

`MIN()`

Aggregate Function

`AVG()`

Aggregate Function

`HAVING`

Clause

Column References

Column References

```
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 example query will count the number of movies per rating, and will:

`GROUP BY`

column`2`

(`rating`

)`ORDER BY`

column`1`

(`total_movies`

)

`SUM()`

Aggregate Function

`SUM()`

Aggregate Function

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

The `SUM()`

aggregate function takes the name of a column as an argument and returns the sum of all the value in that column.

`MAX()`

Aggregate Function

`MAX()`

Aggregate Function

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

The `MAX()`

aggregate function 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 `amount`

column.

`COUNT()`

Aggregate Function

`COUNT()`

Aggregate Function

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

The `COUNT()`

aggregate function 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 `*`

. Unlike `COUNT(*)`

, this variation `COUNT(column)`

will not count `NULL`

values in that column.

`GROUP BY`

Clause

`GROUP BY`

Clause

```
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 similar records. The `GROUP BY`

clause can come after `FROM`

or `WHERE`

but must come before any `ORDER BY`

or `LIMIT`

clause.

The given query will count the number of movies per rating.

`MIN()`

Aggregate Function

`MIN()`

Aggregate Function

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

The `MIN()`

aggregate function 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

`AVG()`

Aggregate Function

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

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

`HAVING`

Clause

`HAVING`

Clause

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

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

Aggregate Functions

Aggregate Functions

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

Aggregate functions perform a calculation on a set of values and return a single value:

`COUNT()`

`SUM()`

`MAX()`

`MIN()`

`AVG()`

`ROUND()`

Function

`ROUND()`

Function

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

- 1We’ve learned how to write queries to retrieve information from the database. Now, we are going to learn how to perform calculations using SQL. Calculations performed on multiple rows of a table a…
- 7Oftentimes, we will want to calculate an aggregate for data with certain characteristics. For instance, we might want to know the mean IMDb ratings for all movies each year. We could calculate ea…
- 8Sometimes, we want to GROUP BY a calculation done on a column. For instance, we might want to know how many movies have IMDb ratings that round to 1, 2, 3, 4, 5. We could do this using the follow…

## What you'll create

Portfolio projects that showcase your new skills

## How you'll master it

Stress-test your knowledge with quizzes that help commit syntax to memory