How to Perform Conditional Aggregation in MySQL without 'SUM IF' and 'COUNT IF'?

MySQL: Alternative Methods for Conditional Aggregation with 'SUM IF' and 'COUNT IF'

Consider the following scenario: you have a table with columns named 'hour' and 'kind' and wish to perform calculations based on a condition. For example, you might want to count the number of rows where the 'kind' column is equal to 1 or sum the 'hour' column only for rows where the 'kind' column is 2.

In MySQL, the syntax for using 'SUM IF' or 'COUNT IF' is not directly supported. Instead, you can use the CASE statement to achieve conditional aggregation:

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

This query counts the total number of rows, sums the 'hour' column for all rows, and counts the number of rows where the 'kind' column is equal to 1.

For more information on conditional aggregation in MySQL, refer to the following resources:

• MySQL documentation: [CASE Functions](https://dev.mysql.com/doc/refman/8.0/en/case-statement.html)
• Stack Overflow topic: [MySQL SUM IF field b = field a](https://stackoverflow.com/questions/2200315/mysql-sum-if-field-b-field-a)

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