Get startedGet started for free

Group and recode values

There are almost 150 distinct values of evanston311.category. But some of these categories are similar, with the form "Main Category - Details". We can get a better sense of what requests are common if we aggregate by the main category.

To do this, create a temporary table recode mapping distinct category values to new, standardized values. Make the standardized values the part of the category before a dash ('-'). Extract this value with the split_part() function:

split_part(string text, delimiter text, field int)

You'll also need to do some additional cleanup of a few cases that don't fit this pattern.

Then the evanston311 table can be joined to recode to group requests by the new standardized category values.

This exercise is part of the course

Exploratory Data Analysis in SQL

View Course

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Fill in the command below with the name of the temp table
DROP TABLE IF EXISTS ___;

-- Create and name the temporary table
CREATE ___ ___ ___ AS
-- Write the select query to generate the table with distinct values of category and standardized values
  SELECT DISTINCT category, 
         ___(___(___, ___, ___)) AS standardized
    -- What table are you selecting the above values from?
    FROM ___;
    
-- Look at a few values before the next step
SELECT DISTINCT standardized 
  FROM recode
 WHERE standardized LIKE 'Trash%Cart'
    OR standardized LIKE 'Snow%Removal%';
Edit and Run Code