SQL counts of support jobs by category and status

2025-hsc-se-q21 · Code Response · 4 marks

Source: NESA 2025 HSC Software Engineering HSC Q21

Question

Below is the Issues table for a technical support company. FixTime, in minutes, is the time taken to complete each job.

JobID	Issue	Response	FixTime	Category	Status
0001	Cannot connect to the internet	Clear cache	22	Software	Pending
0002	Installation failure	Reinstall the printer drivers	76	Hardware	Pending
0004	Forgot password	Reset password	97	Software	Resolved
0005	Installation failure	Follow the installation guide	110	Hardware	Escalated
0009	Blue screen error	Run a system diagnostic test	120	Software	Pending
0011	Installation failure	Verify email settings	45	Software	Escalated

Construct a SQL query that displays:

List the results in order from the highest to lowest, based on the number of jobs.

Reveal answer

SELECT Category, Status, COUNT(*) AS Jobs
FROM Issues
WHERE Status IN ('Pending', 'Escalated')
GROUP BY Category, Status
ORDER BY Jobs DESC;

Marks	Description
4	Constructs a correct SQL query using filtering, grouping/counting and descending ordering.
3	Constructs a mostly correct query with minor errors in filtering, grouping or ordering.
2	Provides a query that demonstrates some correct use of SQL aggregation or conditions.
1	Shows some relevant SQL knowledge.

The query needs to count jobs for each Category and Status combination, exclude resolved jobs, and order the grouped counts from highest to lowest.