Civic Grievances Filed per Department

Easy
74.

Civic Grievances Filed per Department

EasyAggregation

MySQL

The municipal corporation's grievance portal routes every complaint to one department. Complaints marked rejected were found to be duplicates or spam and must not be counted.

Return each department that has at least one counted complaint, with the columns department and complaints (the number of counted complaints), ordered by complaints descending, then department ascending.

Tables

Table: grievances

ColumnTypeValues
grievance_idint
citizen_namevarchar
departmentenumWater Supply, Roads, Sanitation, Electricity, Street Lighting
filed_ondate
statusenumopen, in_progress, resolved, rejected

Primary key: grievance_id.

One row per complaint filed on the portal.

Example

The rows must come back in the order shown.

grievances

grievance_idcitizen_namedepartmentfiled_onstatus
1Priya NairWater Supply2024-07-01resolved
2Rahul RaoRoads2024-07-02open
3Sneha IyerWater Supply2024-07-02in_progress
4Kabir KhanSanitation2024-07-03rejected
5Diya DasRoads2024-07-04resolved
6Arjun MehtaElectricity2024-07-05open
7Meera JoshiSanitation2024-07-05rejected
8Rohan BoseElectricity2024-07-06resolved

Output

departmentcomplaints
Electricity2
Roads2
Water Supply2
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Civic Grievances Filed per Department — SQL Problem

The municipal corporation's grievance portal routes every complaint to one department.

  • Difficulty: Easy
  • Topics: Aggregation
  • Dialect: MySQL
  • Problem: #74

Problem statement

The municipal corporation's grievance portal routes every complaint to one department. Complaints marked rejected were found to be duplicates or spam and must not be counted.

Return each department that has at least one counted complaint, with the columns department and complaints (the number of counted complaints), ordered by complaints descending, then department ascending.

Tables

Table: grievances

ColumnType
grievance_idint
citizen_namevarchar
departmentenum(Water Supply, Roads, Sanitation, Electricity, Street Lighting)
filed_ondate
statusenum(open, in_progress, resolved, rejected)

Primary key: grievance_id.

One row per complaint filed on the portal.

Examples

Example 1

grievances

grievance_idcitizen_namedepartmentfiled_onstatus
1Priya NairWater Supply2024-07-01resolved
2Rahul RaoRoads2024-07-02open
3Sneha IyerWater Supply2024-07-02in_progress
4Kabir KhanSanitation2024-07-03rejected
5Diya DasRoads2024-07-04resolved
6Arjun MehtaElectricity2024-07-05open
7Meera JoshiSanitation2024-07-05rejected
8Rohan BoseElectricity2024-07-06resolved

Output

departmentcomplaints
Electricity2
Roads2
Water Supply2

How to solve Civic Grievances Filed per Department

Filtering in WHERE happens before grouping, so WHERE status <> 'rejected' removes the spam rows and GROUP BY department then counts what is left. A department whose every complaint was rejected has no rows left and simply produces no group, which is exactly what "at least one counted complaint" asks for.

The alternative counts conditionally instead: every department forms a group, a CASE adds 1 only for non-rejected rows, and HAVING … > 0 removes the departments that would show a zero. Both read the table once.

Equal counts are common in small wards, so the order is complaints DESC and then the department name, which makes the answer fully determined. status is never NULL here; if it could be, status <> 'rejected' would drop NULL rows too, and the CASE version would keep them — a difference worth noticing when the column allows NULLs.

Reference solution (MySQL)

SELECT department, COUNT(*) AS complaints
FROM grievances
WHERE status <> 'rejected'
GROUP BY department
ORDER BY complaints DESC, department

Another way

SELECT department, SUM(CASE WHEN status = 'rejected' THEN 0 ELSE 1 END) AS complaints
FROM grievances GROUP BY department
HAVING SUM(CASE WHEN status = 'rejected' THEN 0 ELSE 1 END) > 0
ORDER BY complaints DESC, department

← Library Loans Overdue on Audit Day · Concerts Without a Single Sold Ticket →