How to Perform Conditional Aggregation with SUM and COUNT in MySQL?

Conditional Aggregation with SUM and COUNT in MySQL

When working with relational databases, it is often necessary to perform conditional aggregations to summarize data based on specific criteria. MySQL offers several ways to achieve this, including the use of the IF() and CASE statements.

Using the IF() Statement

In your example, you attempted to use the IF() statement to calculate the count of rows where kind is equal to 1. However, MySQL does not allow the use of the IF() statement in aggregate functions like SUM and COUNT. This is because IF() returns a scalar value, while aggregate functions require a column expression as input.

Using the CASE Statement

To perform conditional aggregation, you can use the CASE statement. The CASE statement allows you to specify multiple conditions and return different values based on those conditions.

The following query uses the CASE statement to calculate the count of rows where kind is equal to 1:

SELECT
    COUNT(id),
    SUM(hour) AS totHour,
    SUM(CASE
        WHEN kind = 1 THEN 1
        ELSE 0
    END) AS countKindOne
FROM your_table;

In this query:

• The CASE statement is used to evaluate the condition kind = 1.
• If the condition is TRUE, it returns 1.
• If the condition is FALSE, it returns 0.
• The result of the CASE statement is then passed to the SUM() aggregate function.

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