ComeçarComece de graça

Using FILTER to create a pivot table

In the previous exercise, you wrote a query that provided information on the number of parking violations (by their numerical code) issued by each of four agencies. The results contained all of the desired information but were presented in a format that included a duplicate display of each violation_code up to four times (for every issuing_agency selected) in the results. A more compact representation of the same data can be achieved through the creation of a pivot table.

In this exercise, you will write a query using the FILTER clause to produce results in a pivot table format. This improved presentation of the data can more easily be used in the report for parking violations issued by each of the four agencies of interest.

Este exercício faz parte do curso

Cleaning Data in PostgreSQL Databases

Ver curso

Instruções do exercício

  • Define the Police column as the number of records for each violation_code with an issuing_agency value of P.
  • Define the Sanitation column as the number of records for each violation_code with an issuing_agency value of S.
  • Define the Parks column as the number of records for each violation_code with an issuing_agency value of K.
  • Define the Transportation column as the number of records for each violation_code with an issuing_agency value of V.

Exercício interativo prático

Experimente este exercício completando este código de exemplo.

SELECT 
	violation_code, 
    -- Define the "Police" column
	COUNT(issuing_agency) FILTER (WHERE issuing_agency = '___') AS "Police",
    -- Define the "Sanitation" column
	COUNT(___) FILTER (WHERE ___ = '___') AS "___",
    -- Define the "Parks" column
	___(___) ___ (WHERE ___ = '___') AS "___",
    -- Define the "Transportation" column
	___(___) ___ (___ ___ = '___') AS "___"
FROM 
	parking_violation 
GROUP BY 
	violation_code
ORDER BY 
	violation_code
Editar e executar o código