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.

この演習はコースの一部です

Cleaning Data in PostgreSQL Databases

コースを見る

演習の手順

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.

実践的なインタラクティブ演習

このサンプルコードを完成させて、この演習に挑戦してみましょう。

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

コードを編集して実行