Add aggregate function SUM_IF()

Stevoisiak commented 1 year ago

Feature Request

Implement a SUM_IF(x, y) aggregate function that would return the sum of TRUE input values. This would be equivalent to SUM(CASE WHEN x THEN y END)

Desired Functionality

Add a function similar to COUNT_IF(x) that would allow developers to simplify their code and improve readability of conditional sums.

Example Usage

Take the following example of conditional sums:

SELECT
    key_column,
    SUM(CASE WHEN category = "sports" THEN budget END) AS sports_budget,
    SUM(CASE WHEN category = "food" THEN budget END) AS food_budget,
    SUM(CASE WHEN category = "advertising" THEN budget END) AS advertising_budget
FROM table_name
GROUP BY 1

Adding a SUM_IF() function would allow it to be rewritten as:

SELECT
    key_column,
    SUM_IF(category = "sports", budget) AS sports_budget,
    SUM_IF(category = "food", budget) AS food_budget,
    SUM_IF(category = "advertising", budget) AS advertising_budget
FROM table_name
GROUP BY 1

Additional Information

The syntax could optionally allow for a third parameter n to allow specifying a fallback SUM value that would avoid NULL values. So for example, SUM_IF(condition, value, 0) would be equivalent to SUM(CASE WHEN condition THEN value ELSE 0 END).