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.
Cet exercice fait partie du cours
Cleaning Data in PostgreSQL Databases
Instructions
- 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).
Exercice interactif pratique
Essayez cet exercice en complétant cet exemple de code.
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;