Functions, sorting, and grouping

1. Functions, sorting, and grouping

Now, that we know about data types and their conversion, let's see what functions Snowflake offers.

2. String functions - INITCAP

Let's start with string functions, which are essential when working with text data, helping us shape and display information the way we want. First up is `INITCAP`, a function specific to Snowflake SQL that capitalizes each word in a string! Here, we call `INITCAP()` on the `name` column of the `pizza_type` table, returning capitalized words!

3. String functions - CONCAT

Snowflake also supports many functions that exist in PostgreSQL. One example is `CONCAT`, which is used to join strings together. To use it, we call `CONCAT()` and place the strings we want to merge inside the parentheses. For instance, our `pizza_type` table has a column called `category`. Imagine wanting each category to read 'Classic - Pizza' instead of just 'Classic'. We combine the category column with the string ' - Pizza' using CONCAT. The result? A new column called 'pizza_category' with our newly formatted names.

4. DATE & TIME functions

Handling dates and times is often tricky. Fortunately, Snowflake has easy-to-use functions for this. For example, `CURRENT_DATE` shows us today's date, and current_time shows the exact time. They're also convenient when we need to set default values in our data or compare dates or times in our data.

5. EXTRACT

Another useful function is `EXTRACT`. It allows us to pull out specific parts like the year, month, or day from a date, time, or timestamp. The <date_or_time_part> is where we specify which part of the date or time we want, be it year, month, day, weekday, etc. Consider extracting the month from the `order_date` timestamp column in the orders table. This allows us to easily count the number of orders per month!

6. SORTING and GROUPING

We know how sorting and grouping works in PostgreSQL. Similarly, in Snowflake, we use `ORDER BY` to sort and `GROUP BY` to group data. What sets Snowflake apart is its unique `GROUP BY ALL` feature. Let's dive into that.

7. GROUP BY ALL

While grouping data, the usual approach is to explicitly list each column in the `GROUP BY` clause. This can be tedious, especially when dealing with numerous columns. `GROUP BY ALL` simplifies this by allowing us to group by all columns in the `SELECT` list without mentioning each one by one. Say we want to determine the average price of pizzas based on their type and size. Traditionally, we would explicitly specify both columns, `pizza_type_id` and `size`, in the `GROUP BY` clause. With `GROUP BY ALL`, we can achieve the same result without listing the columns. Convenient, isn't it?

8. Summary

This table summarizes the syntax we have covered!

9. Let's practice!

It's time to apply all of this with hands-on exercises. Let's jump in!

Create Your Free Account

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