Numeric calculations

1. Numeric calculations

You've gotten your feet wet with text data. Now, let's take a closer look at numeric calculations.

2. Numeric calculations

There is quite a bit that we can do with numeric data. We'll learn more about comparison operators, arithmetic operators, and everyone's favorite, aggregation functions and rounding! Let's start with comparison operators.

3. Comparing numeric values

Comparison operators help us compare or evaluate multiple values. To check if two values are equal, we can use an equal sign. The exclamation-point-equal sign operator validates if two values are unequal. Less than or greater than operators to check if one value less than or greater than another. Adding an equals sign to less that or greater than also checks for equality. Take a look at the example on the right. You'll notice that each of these evaluations returns `true` or `false`. This is the case for all comparison operators, and helps when filtering data!

4. Arithmetic in Snowflake

Arithmetic operators allow us to perform math between numeric values and fields. This includes addition, subtraction, multiplication with the "star" symbol, and division. Take a look at what this looks like on the left! Each of these arithmetic operations can be performed between constant numbers and numeric fields, or any combination of the two.

5. Arithmetic

Here, we're performing three different arithmetic operations. First, we're adding 10 points to each students grade. Then, we multiple the `exam_score` field by the `curve` field to generate a curved score. Finally, we divide the `exam_score` by 2, to created a weighted score for the course. The results look like this!

6. Aggregation functions generate summary data

What if we want to find the average exam score? How would we do this? Aggregation functions generate "summary" data. Using aggregation functions will feel a little like this; you'll call a function like `SUM` or `AVG` within a query to generate a summarized result set.

7. Aggregation functions

We'll look at two aggregations functions; `SUM` and `AVG`. `SUM` takes a column and returns the total of that column, while `AVG` finds the average value of a passed field. The typical format looks like this. A non-aggregated field is added to the `SELECT` statement, and a numeric field is passed to `SUM` or `AVG`. In a query with aggregation functions, we must `GROUP BY` all non-aggregated fields. Otherwise, Snowflake will throw an exception. To make sure you're grouping by the correct fields, you could also use the syntax `GROUP BY ALL`.

8. Aggregation functions

Here, we're finding the total number of `correct_answers` and average `exam_score` for each exam. If you take a closer look, you'll see the `ROUND` function. `ROUND` takes a value to round, and a number to specify the number of decimal points to keep. It's not an aggregation function, but `ROUND` helps to make results more digestible, especially for averages. To make sure we properly aggregate records and avoid an error from Snowflake, we need to `GROUP BY` all non-aggregated fields; in this case `exam_name`.

9. Aggregation functions

The aggregated output will look like this. Remember, these values were generated using the `SUM` and combination of the `AVG` and `ROUND`. The results offer a useful summary for each class, and could be used in a dashboard or report.

10. Let's practice!

Alrighty, it's your turn. Good luck!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.