Learn how to use SQL to perform calculations during a query.

Aggregate Functions

Congratulations!

You just learned how to use aggregate functions to perform calculations on your data. What can we generalize so far?

- [`COUNT()`](https://www.codecademy.com/resources/docs/sql/aggregate-functions/count?page_ref=catalog): count the number of rows
- [`SUM()`](https://www.codecademy.com/resources/docs/sql/aggregate-functions/sum?page_ref=catalog): the sum of the values in a column
- [`MAX()`](https://www.codecademy.com/resources/docs/sql/aggregate-functions/max?page_ref=catalog)/[`MIN()`](https://www.codecademy.com/resources/docs/sql/aggregate-functions/min?page_ref=catalog): the largest/smallest value
- [`AVG()`](https://www.codecademy.com/resources/docs/sql/aggregate-functions/avg?page_ref=catalog): the average of the values in a column
- [`ROUND()`](https://www.codecademy.com/resources/docs/sql/commands/round?page_ref=catalog): round the values in the column 

*Aggregate functions* combine multiple rows together to form a single value of more meaningful information.

- [`GROUP BY`](https://www.codecademy.com/resources/docs/sql/commands/group-by?page_ref=catalog) is a clause used with aggregate functions to combine data from one or more columns.
- [`HAVING`](https://www.codecademy.com/resources/docs/sql/commands/having?page_ref=catalog) limit the results of a query based on an aggregate property.


Review

The fastest way to calculate how many rows are in a table is to use the `COUNT()` function.

[`COUNT()`](https://www.codecademy.com/resources/docs/sql/aggregate-functions/count?page_ref=catalog) is a function that takes the name of a column as an argument and counts the number of non-empty values in that column.

```sql
SELECT COUNT(*)
FROM table_name;
```

Here, we want to count every row, so we pass `*` as an argument inside the parenthesis.

Count

SQL Queries don't just access raw data, they can also perform calculations on the raw data to answer specific data questions.

Calculations performed on multiple rows of a table are called **aggregates**.

In this lesson, we have given you a table named `fake_apps` which is made up of fake mobile applications data.

Here is a quick preview of some important aggregates that we will cover in the next five exercises:

- [`COUNT()`](https://www.codecademy.com/resources/docs/sql/aggregate-functions/count?page_ref=catalog): count the number of rows
- [`SUM()`](https://www.codecademy.com/resources/docs/sql/aggregate-functions/sum?page_ref=catalog): the sum of the values in a column
- [`MAX()`](https://www.codecademy.com/resources/docs/sql/aggregate-functions/max?page_ref=catalog)/[`MIN()`](https://www.codecademy.com/resources/docs/sql/aggregate-functions/min?page_ref=catalog): the largest/smallest value
- [`AVG()`](https://www.codecademy.com/resources/docs/sql/aggregate-functions/avg?page_ref=catalog): the average of the values in a column
- [`ROUND()`](https://www.codecademy.com/resources/docs/sql/commands/round?page_ref=catalog): round the values in the column

Let's get started!


Introduction

SQL uses the [`AVG()`](https://www.codecademy.com/resources/docs/sql/aggregate-functions/avg?page_ref=catalog) function to quickly calculate the average value of a particular column.

The statement below returns the average number of downloads for an app in our database:

```sql
SELECT AVG(downloads)
FROM fake_apps;
```

The `AVG()` function works by taking a column name as an argument and returns the average value for that column.

Average

Sometimes, we want to [`GROUP BY`](https://www.codecademy.com/resources/docs/sql/commands/group-by?page_ref=catalog) 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 following syntax:

```sql
SELECT ROUND(imdb_rating),
   COUNT(name)
FROM movies
GROUP BY ROUND(imdb_rating)
ORDER BY ROUND(imdb_rating);
```

However, this query may be time-consuming to write and more prone to error.

SQL lets us use column reference(s) in our `GROUP BY` that will make our lives easier.

- `1` is the first column selected
- `2` is the second column selected
- `3` is the third column selected

and so on.

The following query is equivalent to the one above:

```sql
SELECT ROUND(imdb_rating),
   COUNT(name)
FROM movies
GROUP BY 1
ORDER BY 1;
```

Here, the `1` refers to the first column in our `SELECT` statement, `ROUND(imdb_rating)`.


Group By II

By default, SQL tries to be as precise as possible without rounding. We can make the result table easier to read using the `ROUND()` function.

[`ROUND()`](https://www.codecademy.com/resources/docs/sql/commands/round?page_ref=catalog) function takes two arguments inside the parenthesis: 

1. a column name
2. an integer 

It rounds the values in the column to the number of decimal places specified by the integer. 

```sql
SELECT ROUND(price, 0)
FROM fake_apps;
```

Here, we pass the column `price` and integer `0` as arguments. SQL rounds the values in the column to 0 decimal places in the output.

Round

The `MAX()` and `MIN()` functions return the highest and lowest values in a column, respectively.

How many downloads does the most popular app have?

```sql
SELECT MAX(downloads)
FROM fake_apps;
```

The most popular app has 31,090 downloads!

[`MAX()`](https://www.codecademy.com/resources/docs/sql/aggregate-functions/max?page_ref=catalog) takes the name of a column as an argument and returns the largest value in that column. Here, we returned the largest value in the `downloads` column.

[`MIN()`](https://www.codecademy.com/resources/docs/sql/aggregate-functions/min?page_ref=catalog) works the same way but it does the exact opposite; it returns the smallest value.

Max / Min

In addition to being able to group data using [`GROUP BY`](https://www.codecademy.com/resources/docs/sql/commands/group-by?page_ref=catalog), SQL also allows you to filter which groups to include and which to exclude. 

For instance, imagine that we want to see how many movies of different genres were produced each year, but we only care about years and genres with at least 10 movies. 

We can't use [`WHERE`](https://www.codecademy.com/resources/docs/sql/commands/where?page_ref=catalog) here because we don't want to filter the rows; we want to *filter groups*.

This is where [`HAVING`](https://www.codecademy.com/resources/docs/sql/commands/having?page_ref=catalog) comes in. 

`HAVING` is very similar to `WHERE`. In fact, all types of `WHERE` clauses you learned about thus far can be used with `HAVING`.

We can use the following for the problem:

```sql
SELECT year,
   genre,
   COUNT(name)
FROM movies
GROUP BY 1, 2
HAVING COUNT(name) > 10;
```

- When we want to limit the results of a query based on values of the individual rows, use `WHERE`.
- When we want to limit the results of a query based on an aggregate property, use `HAVING`. 

`HAVING` statement always comes after `GROUP BY`, but before [`ORDER BY`](https://www.codecademy.com/resources/docs/sql/commands/order-by?page_ref=catalog) and [`LIMIT`](https://www.codecademy.com/resources/docs/sql/commands/limit?page_ref=catalog).


Having

Oftentimes, 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 each number by a series of queries with different `WHERE` statements, like so:

```sql
SELECT AVG(imdb_rating)
FROM movies
WHERE year = 1999;

SELECT AVG(imdb_rating)
FROM movies
WHERE year = 2000;

SELECT AVG(imdb_rating)
FROM movies
WHERE year = 2001;
```

and so on. 

Luckily, there's a better way!

We can use [`GROUP BY`](https://www.codecademy.com/resources/docs/sql/commands/group-by?page_ref=catalog) to do this in a single step:

```sql
SELECT year,
   AVG(imdb_rating)
FROM movies
GROUP BY year
ORDER BY year;
```

`GROUP BY` is a clause in SQL that is used with aggregate functions. It is used in collaboration with the `SELECT` statement to arrange identical data into *groups*.

 The `GROUP BY` statement comes after any [`WHERE`](https://www.codecademy.com/resources/docs/sql/commands/where?page_ref=catalog) statements, but before [`ORDER BY`](https://www.codecademy.com/resources/docs/sql/commands/order-by?page_ref=catalog) or [`LIMIT`](https://www.codecademy.com/resources/docs/sql/commands/limit?page_ref=catalog).

Group By I

SQL makes it easy to add all values in a particular column using `SUM()`.

[`SUM()`](https://www.codecademy.com/resources/docs/sql/aggregate-functions/sum?page_ref=catalog) is a function that takes the name of a column as an argument and returns the sum of all the values in that column.

What is the total number of downloads for all of the apps combined?

```sql
SELECT SUM(downloads)
FROM fake_apps;
```

This adds all values in the `downloads` column.
