Applying aggregate functions to converted values
As demonstrated in the video exercise, converting a column's value from TEXT to a number allows for calculations to be performed using aggregation functions. The summons_number is of type TEXT in the parking_violation dataset. The maximum (using MAX(summons_number)) and minimum (using MIN(summons_number)) of the TEXT representation summons_number can be calculated. If you, however, want to know the size of the range (max - min) of summon_number values , this calculation is not possible because the operation of subtraction on TEXT types is not defined. First, converting summons_number to a BIGINT will resolve this problem.
In this exercise, you will calculate the size of the range of summons_number values as the difference between the maximum and minimum summons_number.
Cet exercice fait partie du cours
Cleaning Data in PostgreSQL Databases
Instructions
- Define the
range_sizeforsummons_numberas the difference between the maximumsummons_numberand the minimum of thesummons_numberusing thesummons_numbercolumn after converting to theBIGINTtype.
Exercice interactif pratique
Essayez cet exercice en complétant cet exemple de code.
SELECT
-- Define the range_size from the max and min summons number
___(___) - ___(___) AS ___
FROM
parking_violation;