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.

Bu egzersiz

Cleaning Data in PostgreSQL Databases

kursunun bir parçasıdır

Kursu Görüntüle

Egzersiz talimatları

Include violation_code and issuing_agency in the SELECT list for the query.
For each violation_code and issuing_agency pair, include the number of records containing the pair in the SELECT list.
Restrict the results to the agencies of interest based on their single-character code (P, S, K, V).

Uygulamalı interaktif egzersiz

Bu örnek kodu tamamlayarak bu egzersizi bitirin.

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;

Kodu Düzenle ve Çalıştır