I’m just curious: I know something like
SUM(string = 'anyString') over (partition by ANY_COLUMN)
or
SUM(string like 'anyWildcard%') over (partition by ANY_COLUMN)
is working in MySQL/MariaDB. What would be the equivalent in Oracle?
>Solution :
The ANSI/ISO SQL standard way is to use case expressions to do conditional aggregation:
SUM(case when string = 'anyString' then 1 else 0 end) over (partition by ANY_COLUMN)
Standards compliance note:
Using a case expression is Core SQL functionality, i.e. expected to work on any dbms.
FILTER is an optional feature of the ANSI/ISO SQL standard. ‘Without Feature T612, “Advanced OLAP operations”, conforming SQL language shall not contain a < filter clause >.‘