Use conditional aggregate functions to transform data based on a set of conditions.

Conditional Aggregates

**Aggregate functions** compute a single result from a set of multiple input values. You can think of aggregate data as data collected from multiple rows at a time. In this lesson, we'll continue learning about aggregate functions by focusing on conditionals, sums, and combining aggregate functions. 

**Conditional Aggregates** are aggregate functions that compute a result set based on a given set of conditions. 

If you are new to aggregate functions, we recommend you complete <a href="https://www.codecademy.com/learn/learn-sql" target="_blank">this course</a> first.


Aggregate Functions Revisited

Congratulations! You just learned about Conditional Aggregates in SQL. What can we generalize so far?


* **Conditional Aggregates** are aggregate functions the compute a result set based on a given set of conditions. 
- `NULL` can be used to denote an empty field value
- `CASE` statements allow for custom classification of data
- `CASE` statements can be used inside aggregates (like `SUM()` and `COUNT()`) to provide filtered measures

Generalizations

Modify the previous elevation example to find the percentage of high elevation airports `(elevation >= 2000)` by state.

Combining aggregates II

Oftentimes we'd like to combine aggregates, to create percentages or ratios. 

In the instance of the last query, we might want to find out the percent of flight distance that is from United by origin airport. We can do this simply by using the mathematical operators we need in SQL: 

```sql
SELECT origin, 
	(100.0*(sum(CASE WHEN carrier = 'UN' THEN distance ELSE 0 END))/sum(distance)) as percentage_flight_distance_from_united FROM flights 
GROUP BY origin;
```



Combining aggregates

Almost every programming language has a way to represent "if, then, else", or conditional logic. In SQL, we represent this logic with the `CASE` statement, as follows:

```sql
SELECT
	CASE
		WHEN elevation < 500 THEN 'Low'
		WHEN elevation BETWEEN 500 AND 1999 THEN 'Medium'
		WHEN elevation >= 2000 THEN 'High'
		ELSE 'Unknown'
	END AS elevation_tier
	, COUNT(*)
FROM airports
GROUP BY 1;
```

In the above statement, `END` is required to terminate the statement, but `ELSE` is optional. If `ELSE` is not included, the result will be `NULL`. Also notice the shorthand method of referencing columns to use in `GROUP BY`, so we don't have to rewrite the entire Case Statement.

CASE WHEN

We can do that same thing for other aggregates like `SUM()`. For instance, if we wanted to sum the total flight distance and compare that to the sum of flight distance from a particular airline (in this case, United Airlines) by origin airport, we could run the following query: 

```sql
SELECT origin, sum(distance) as total_flight_distance, sum(CASE WHEN carrier = 'UA' THEN distance ELSE 0 END) as total_united_flight_distance 
FROM flights 
GROUP BY origin;
```



SUM(CASE WHEN )

Sometimes you want to look at an entire result set, but want to implement conditions on certain aggregates. 

For instance, maybe you want to identify the total amount of airports as well as the total amount of airports with high elevation in the same result set. We can accomplish this by putting a `CASE WHEN` statement in the aggregate.

```sql
SELECT	state, 
	COUNT(CASE WHEN elevation >= 2000 THEN 1 ELSE NULL END) as count_high_elevation_aiports 
FROM airports 
GROUP BY state;
```



COUNT(CASE WHEN )

While working with databases, it's common to have empty or unknown "cells" in data tables. For example, in our first `flights` table, we keep track of when flights arrive in a column called `arr_time`:

![alt text](https://content.codecademy.com/courses/SQL/chart-02.svg "Flights Table")

In this table, the third flight was canceled and therefore did not have an arrive time. We'll leave this cell empty, which is denoted as `NULL`.

What do we do when we need to test whether a value is or is not null? We use the special keywords `IS NULL` or `IS NOT NULL` in the `WHERE` clause (`= NULL` does not work).