Selecting data for a pivot table
In an effort to get a better understanding of which agencies are responsible for different types of parking violations, you have been tasked with creating a report providing these details. This report will focus on four issuing agencies: Police Department (P), Department of Sanitation (S), Parks Department (K), and Department of Transportation (V). All of the records required to create such a report are present in the parking_violations table. An INTEGER violation_code and CHAR issuing_agency is recorded for every parking_violation.
In this exercise, you will write a SELECT query that provides the underlying data for your report: the parking violation code, the issuing agency code, and the total number of records with each pair of values.
この演習はコースの一部です
Cleaning Data in PostgreSQL Databases
演習の手順
- Include
violation_codeandissuing_agencyin theSELECTlist for the query. - For each
violation_codeandissuing_agencypair, include the number of records containing the pair in theSELECTlist. - Restrict the results to the agencies of interest based on their single-character code (
P,S,K,V).
実践的なインタラクティブ演習
このサンプルコードを完成させて、この演習に挑戦してみましょう。
SELECT
-- Include the violation code in results
___,
-- Include the issuing agency in results
___,
-- Number of records with violation code/issuing agency
___(___)
FROM
parking_violation
WHERE
-- Restrict the results to the agencies of interest
___ IN (___, ___, ___, ___)
GROUP BY
-- Define GROUP BY columns to ensure correct pair count
___, ___
ORDER BY
violation_code, issuing_agency;