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.
This exercise is part of the course
Cleaning Data in PostgreSQL Databases
Exercise instructions
- Include
violation_code
andissuing_agency
in theSELECT
list for the query. - For each
violation_code
andissuing_agency
pair, include the number of records containing the pair in theSELECT
list. - Restrict the results to the agencies of interest based on their single-character code (
P
,S
,K
,V
).
Hands-on interactive exercise
Have a go at this exercise by completing this sample 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;