How to Efficiently Count Conditional Rows by Priority in SQL?

SQL efficiently counts conditional rows sorted by priority

Suppose you have a table that stores job information, with a column indicating priority (an integer between 1 and 5). To generate a chart report that displays job counts by priority, you need to create five additional fields, each of which counts the number of rows with a specific priority value.

The solution lies in using the CASE statement in the SELECT query. The following code provides an efficient and performant way to achieve your desired output:

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;

This query essentially uses the SUM() function to count the number of rows for each priority level. However, it also uses the CASE statement to exclude rows that do not meet certain priority conditions. The result is a table with additional fields representing condition counts.

To include only the job count and not the jobID and JobName in the results, simply remove these columns from the SELECT statement and remove the GROUP BY clause.

The above is the detailed content of How to Efficiently Count Conditional Rows by Priority in SQL?. For more information, please follow other related articles on the PHP Chinese website!