Add aggregate function SUM_IF()

[Stevoisiak]

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).

[tdcmeehan]

CC @kaikalur

[ges1227]

I'd like to take on this feature request 😀

[tsafacjo]

I'd like to take on this feature request too.

[tdcmeehan]

Feel free to submit a PR!

[DHRUV6029]

hi @tdcmeehan is this still open can i work on this

[tdcmeehan]

@DHRUV6029 yes it's still open.

[DHRUV6029]

hello @tdcmeehan @Stevoisiak @ges1227 I have submitted the pr #19060 for this feature can you please review it please.

thanks