How to Count Conditional Column Values in SQL: A Priority-Based Example?

SQL conditional column count

Consider a table called "Jobs" with the following structure:

<code>jobId, jobName, Priority</code>

Where "Priority" is an integer between 1 and 5.

Problem statement:

You need to create a query that counts the number of rows for each priority level (1 to 5) in the "Jobs" table. The query should return columns named "Priority1" through "Priority5", respectively, representing these counts.

Solution:

To do this, use the following SQL statement:

<code class="language-sql">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;</code>

Explanation:

• The CASE statement evaluates the "Priority" column of each row.
• If "Priority" is equal to the specified value (1, 2, 3, 4 or 5), then it contributes 1 to the count.
• If "Priority" is not equal to the specified value, it contributes 0.
• The SUM function aggregates these counts for each priority level.
• The GROUP BY clause groups the results by "jobId" and "jobName" (assuming you want to include these columns in the results).

NOTE: If you wish to exclude "jobId" and "jobName", just remove them from the SELECT and GROUP BY clauses.

The above is the detailed content of How to Count Conditional Column Values in SQL: A Priority-Based Example?. For more information, please follow other related articles on the PHP Chinese website!