How to Count Job Priorities Using Conditional SQL Queries?

Use conditional count queries for priority analysis

In data analysis, conditional counting is often needed to extract information from categorical data. Consider a table containing columns "jobId", "jobName" and "Priority", where "Priority" ranges from 1 to 5. We need to create a query that counts the number of rows for each priority level.

Query using CASE statement:

The following query uses a CASE statement to perform conditional counting for each priority level:

SELECT 
    jobID, 
    jobName,
    SUM(CASE WHEN Priority = 1 THEN 1 ELSE 0 END) AS Priority1,
    SUM(CASE WHEN Priority = 2 THEN 1 ELSE 0 END) AS Priority2,
    SUM(CASE WHEN Priority = 3 THEN 1 ELSE 0 END) AS Priority3,
    SUM(CASE WHEN Priority = 4 THEN 1 ELSE 0 END) AS Priority4,
    SUM(CASE WHEN Priority = 5 THEN 1 ELSE 0 END) AS Priority5
FROM 
    Jobs
GROUP BY 
    jobID, 
    jobName

For each row, the CASE statement checks whether the "Priority" value matches the specified number (for example, Priority1 is 1). The expression returns 1 if true and 0 if false. The SUM function then aggregates these values ​​against each group (determined by "jobId" and "jobName").

Remove jobID and jobName:

If you don't need "jobId" and "jobName" included in the results, you can remove them by modifying the SELECT and GROUP BY clauses:

SELECT 
    SUM(CASE WHEN Priority = 1 THEN 1 ELSE 0 END) AS Priority1,
    SUM(CASE WHEN Priority = 2 THEN 1 ELSE 0 END) AS Priority2,
    SUM(CASE WHEN Priority = 3 THEN 1 ELSE 0 END) AS Priority3,
    SUM(CASE WHEN Priority = 4 THEN 1 ELSE 0 END) AS Priority4,
    SUM(CASE WHEN Priority = 5 THEN 1 ELSE 0 END) AS Priority5
FROM 
    Jobs

This simplified query will only return the condition count for each priority level.

The above is the detailed content of How to Count Job Priorities Using Conditional SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!