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.

Latihan ini adalah bagian dari kursus

Exploratory Data Analysis in SQL

Lihat Kursus

Petunjuk latihan

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.

Latihan interaktif praktis

Cobalah latihan ini dengan menyelesaikan kode contoh berikut.

-- 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
 ___ ___;

Edit dan Jalankan Kode