How to Achieve 'SUM IF' and 'COUNT IF' Functionality in MySQL with Conditional Aggregation?

MySQL Conditional Aggregation: Unlocking 'SUM IF' and 'COUNT IF' Capabilities

In MySQL, performing conditional aggregation, such as summing or counting values based on specific conditions, can be achieved through the use of CASE statements. This allows you to flexibly apply criteria to your aggregation operations.

Consider the following scenario: you have a table with columns 'hour' and 'kind' (which can hold values 1, 2, or 3). You want to count the number of rows, calculate the total hours, and determine the count of rows where 'kind' is equal to 1.

Initially, you might try the following queries:

SELECT count(id), SUM(hour) as totHour, SUM( IF ( kind = 1, 1, 0 ) ) as countKindOne

or

SELECT count(id), SUM(hour) as totHour, COUNT( IF ( kind = 1 ) ) as countKindOne

However, these queries will likely result in an error. To resolve this, you need to utilize a CASE statement within the aggregation operations:

SELECT count(id), 
    SUM(hour) as totHour, 
    SUM(case when kind = 1 then 1 else 0 end) as countKindOne

In this CASE statement, we specify that if the value in the 'kind' column is 1, we return 1, otherwise we return 0. This allows us to conditionally count the rows where 'kind' is equal to 1.

The above is the detailed content of How to Achieve 'SUM IF' and 'COUNT IF' Functionality in MySQL with Conditional Aggregation?. For more information, please follow other related articles on the PHP Chinese website!