How can I use conditional aggregation in MySQL to count and sum values based on specific criteria?

MySQL: Conditional Aggregation with 'SUM IF' and 'COUNT IF'

Aggregation functions like SUM and COUNT are essential for summarizing data in MySQL. However, what if you need to apply conditions to your aggregation? This is where conditional aggregation comes into play.

Consider a scenario where you have a table with two columns: 'hour' and 'kind'. You want to count and sum 'hour' values based on whether the corresponding 'kind' value is 1. Traditional aggregation functions alone cannot handle this.

To solve this problem, MySQL offers the CASE statement, which allows you to define custom conditions for aggregation. Here's a revised query using a CASE statement:

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

In this query, the CASE statement checks whether the 'kind' value is equal to 1. If true, it returns 1; otherwise, it returns 0. The result is then summed using the SUM() function to count the occurrences of 'kind' equal to 1.

By using conditional aggregation with a CASE statement, you can effectively filter and aggregate your data based on specific criteria. This powerful technique opens up a wide array of possibilities for data analysis and manipulation in MySQL.

The above is the detailed content of How can I use conditional aggregation in MySQL to count and sum values based on specific criteria?. For more information, please follow other related articles on the PHP Chinese website!