1. Calculating values in a query
Now that you know how to customize your SQL queries using filtering and aggregation functions,
2. Math operators
it's time to dive deeper by performing typical and useful math operations such as addition, subtraction, multiplication, et cetera on columns in our query. It is import to remember that these operations perform differently with non-numeric data types.
3. Calculating difference
If we wanted to find the top five age groups by growth between 2000 and 2008, we would start by passing select the age column and then the calculated difference between pop2008 and pop2000 columns. Notice that we wrapped the difference in parenthesis so we can apply the label pop_change to it. Next we're going to group by age and order by the pop_change in descending manner and finally we apply a limit statement to only return the top 5 results. Now we can execute that statement and print the results. That let's us see that the number of 61 and 85 year olds grew quite a bit between those years.
4. Case statement
Often when we are performing calculations, we want to selectively include data in a calculation based on a set of conditions. The case statement allows us to do just that. The case statement has a list of conditions and a column to return if the condition is meet, and it ends with an else that tells it how to handle those rows without a match.
5. Case example
Let's take a look at how a case statement works. Before we begin to look at this example, this is just to demonstrate how case works, we could get the same result where a much simpler select statement and a where clause. However, we'll be building on the case statement in the next example to build queries that a where clause cannot perform. We start by importing the case statement from sqlalchemy. Then we build a select statement that include a sum function for a case statement. The case statement begins with a conditional that checks to see if the state is New York, and if that is the case it returns the value of the pop2008 column. Next we have an else clause that returns 0 for any record that does not have the state of New York. Then we execute the statement and print the results.
6. Cast statement
The cast statement is also useful when we are performing operations and you need to convert a column from one type to another. This is useful for converting integers to floats so we get the expected result when we use it in division. It can also be used to convert strings to dates. The case statement accepts a column or expression and the type to which you want to convert it. Let's combine the case and cast statements in an example.
7. Percentage example
If we wanted to find what percentage of the total population live in New York. We start by importing case, cast and float. Then we build a select statement where we are selecting a very complex clause. To calculate a percentage, we need sum the 2008 population for all the rows where the state is New York and dividing it by the sum of the total 2008 population and multiple by 100. We do that by calculating the sum of a case statement that returns the pop2008 column if the state is New York and 0 for any other record just like our last example. Then we divide that by the sum of the pop2008 column for all the records. However, we are casting that to a Float so we will get a fractional result when we perform the division. This is important because if we don't covert it, it will perform floor or integer division and we'll get 0 back. Next we multiply that by 100 to get the percentage and label the entire calculation as ny_percent. Next, we can execute that statement and print the results.
Notice that we have used a sophisticated SQL query to extract the solution to a very intuitive question from our database: What percentage of the total population lived in New York in 2008?
8. Let's practice!
Now it's your turn to play!