How to count highest status values per ID in SQL query

Data and Databases
1

I need help with a SQL query that counts the highest status value for each unique ID in my table.

I have a support request tracking system where each request goes through different stages. The database logs every status change with these values:

  • 0 = submitted
  • 1 = assigned
  • 2 = completed
  • 5 = rejected

I want to create reports showing how many requests each employee completed, assigned, etc.

My table structure:

Request_ID Employee_Name Request_Status
201 John Smith 2
201 Mike Johnson 0
200 John Smith 2
200 Sarah Davis 0
199 Tom Wilson 0
198 John Smith 2
198 Lisa Brown 0
197 John Smith 2
197 David Miller 0

Each request can have multiple rows showing the progression from submitted to final status. I need to find the maximum status reached for each request_id and count those by employee.

My current attempt:

select employee_name, count(request_status) as total_count from support_log_table 
group by employee_name
order by total_count desc, employee_name desc

This gives me an aggregate function error. How can I properly count the maximum status values per request for each employee?

2

your query’s broken cuz ur grouping by employee without gettin the max status per request first. use a window function to grab the highest status for each request_id, then group by employee. try this: select employee_name, count(*) from (select request_id, employee_name, max(request_status) over (partition by request_id) as max_status from support_log_table) subquery group by employee_name

3

The problem is figuring out which employee owns each request’s highest status before you can count anything. I’ve hit this same issue building audit reports - a two-step approach works best. First, find the max status per request and grab the employee tied to it. Then count those up.

Here’s what I use:

WITH max_status_per_request AS (
    SELECT request_id, 
           employee_name,
           request_status,
           ROW_NUMBER() OVER (PARTITION BY request_id ORDER BY request_status DESC) as rn
    FROM support_log_table
)
SELECT employee_name, 
       COUNT(*) as requests_handled
FROM max_status_per_request 
WHERE rn = 1
GROUP BY employee_name
ORDER BY requests_handled DESC;

This handles situations where multiple employees have the same max status on a request - it just takes the first one. ROW_NUMBER makes sure you get exactly one row per request.

4

interesting challenge! are you counting how many requests each employee brought to their highest status? or do you want separate counts for each status level they hit? also, if a request gets rejected after assignment - does the employee get credit for the rejection or the assignment?