Coalesce

The coalesce() function can be useful for specifying a default or backup value when a column contains NULL values.

coalesce() checks arguments in order and returns the first non-NULL value, if one exists.

coalesce(NULL, 1, 2) = 1
coalesce(NULL, NULL) = NULL
coalesce(2, 3, NULL) = 2

In the fortune500 data, industry contains some missing values. Use coalesce() to use the value of sector as the industry when industry is NULL. Then find the most common industry.

Deze oefening maakt deel uit van de cursus

Exploratory Data Analysis in SQL

Cursus bekijken

Oefeninstructies

Use coalesce() to select the first non-NULL value from industry, sector, or 'Unknown' as a fallback value.
Alias the result of the call to coalesce() as industry2.
Count the number of rows with each industry2 value.
Find the most common value of industry2.

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

-- Use coalesce
SELECT ___(___, ___, 'Unknown') AS industry2,
       -- Don't forget to count!
       ___ 
  FROM ___ 
-- Group by what? (What are you counting by?)
 GROUP BY ___
-- Order results to see most common first
 ___ ___ ___ ___
-- Limit results to get just the one value you want
 ___ ___;

Code bewerken en uitvoeren