Search code examples
sqlfilteraggregatehana

Using a filter in aggregate function in SAP HANA

In PostgreSQL one can use a filter in an aggregate function like for example:

SUM(<expression>) FILTER(WHERE <condition>)

I want to use such a filter (in fact many different ones) on an aggregate function in SAP HANA.

Unfortunately I was not able to find a possibility to do so, so currently I need to do a lot of joins with subquerys using the "normal" WHERE statement to filter the data, which is quite messy.

So my question is: Is there a similar function in SAP HANA to apply a filter on an aggregate function?

Solution

  • As mentioned in comments by @Eldar you can use CASE WHEN, for example:

    COUNT(CASE WHEN <condition> THEN 1 END) as CNT_A
SUM(CASE WHEN <condition> AND <condition2> BETWEEN x AND y THEN <expression> ELSE NULL END) as SUM_X