How to Count Conditional Values in a SQL Column?

Statistics condition values ​​in SQL columns

Suppose you have a column called "Priority" that contains integer values ​​between 1 and 5. You want to generate a chart showing the count for each priority value. For example, "Priority1" should count the number of rows with a "Priority" value of 1, "Priority2" should count the number of rows with a "Priority" value of 2, and so on.

Solution:

To do this, you can use a SQL query with a conditional aggregate function:

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 uses conditional aggregation to count the number of rows for each priority value. The CASE expression checks the value of Priority and returns 1 if it matches the required priority value, otherwise it returns 0. The SUM function then accumulates these values ​​to produce the final count.

If you don't need the jobID and JobName columns in the results, you can remove them from the SELECT clause and remove the GROUP BY clause:

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;

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