3/24/2024 0 Comments Scriptcase group by![]() WHEN population = 500000 and population = 1500000 We will need three separate CASE WHEN statements for this example, one for each condition that we’ve defined in our CASE WHEN statement: low, medium, and high. Continuing with the same example, let’s consider how we would count the number of cities in each population_level. We can also place CASE WHEN statements inside of aggregate functions to count the number of records that meet certain conditions. Example 3: CASE WHEN Statements Inside Aggregations We don’t necessarily need to define the logic for high-population cities if the city fails the first two logic statements, then the population is greater than 1,500,000. WHEN population = 500000 and population = 500000 and population = 1500000 THEN ‘High’ This would look like the following query: You would define the first two population levels and then use ELSE to bucket every other city into high. Example 2: CASE WHEN With ELSE in GROUP BYĪnother way to write this query would be to utilize the ELSE clause. If you need a refresher on the GROUP BY clause, read this article on GROUP BY in SQL. The returned results look like this: population_level Then the average is computed across each of these levels with the GROUP BY. When this query is run, the data is first evaluated in the logical conditions of the CASE WHEN statement and assigned a value for population_level. Note that we have repeated the same CASE WHEN expression in SELECT and in GROUP BY. We’ve added a calculation for the average population. We’ve used the same population category as in the query above. We’ll use the same CASE WHEN statement as above, add in an aggregation for average, and then GROUP BY using the same syntax. In this example, we’ll calculate the average population for each population_level. ![]() Now that we have our custom category of population_level, we can calculate different metrics for it. Using CASE WHEN with GROUP BY Example 1: Aggregations of Custom Categories We’ll demonstrate this in the next section. What if we wanted to find out the average population for each population_level? In this scenario, we can accomplish this by including an aggregation and GROUP BY clause. The returned result looks like this: city We are then aliasing the column as population_level. Finally, if the population is greater than 1,500,000, we assign the value High. If the population is between 500,000 and 1,500,000, then we assign the value Medium. If the population is less than 500,000, then we assign the value of Low. In this query, we’re adding conditional logic based on the population field. WHEN population = 500000 and population = 1500000 THEN ‘High’ If we wanted to categorize each city’s population into low, medium, or high we would run: To illustrate further, consider the following data found in the cities table: city ![]() Otherwise, the CASE WHEN assigns the product to the Other category. If the product is a hat, the CASE WHEN assigns it to the Accessories category. If the product is a shirt, the CASE WHEN assigns it to the Clothing category. This is what CASE WHEN syntax looks like:ĬASE WHEN product = ‘Shirt’ THEN ‘Clothing’ If your data fails all your conditions and you do not use ELSE, the data will return a NULL value. If all logical conditions in the CASE WHEN statement fail, you can use ELSE to assign a value to this data. After your last THEN statement, you use END to close the clause.Īnother optional clause is the ELSE clause. After that you use THEN to define the value if that condition is true. ![]() You begin the statement with CASE WHEN to define your logical condition. Each of these is needed or your query will return an error. ![]() There are three important pieces to CASE statements in SQL: CASE WHEN, THEN, and END. It evaluates conditions and if it finds the condition to be true, it will return a specific defined result. You can think of the CASE WHEN statement as if.then logic for your query. If you’d like more information on the CASE statement, be sure to read our article How to Use CASE in SQL. It’s a great follow-up to this article.įirst, let’s review the CASE WHEN statement with a few examples. For a more in-depth look into these concepts, take our Creating Basic SQL Reports, which will walk you through all the nuances of CASE and GROUP BY. We will explore how these statements work throughout this article. In these types of situations, we can use the CASE WHEN and GROUP BY statements to format data and add metadata to our original dataset. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |