-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: 1.3.0-incubating
-
Component/s: None
-
Labels:None
The SQL standard provides a means to control which values are accumulated by an aggregate function.
aggregate function> ::= COUNT <left paren> <asterisk> <right paren> [ <filter clause> ] | <general set function> [ <filter clause> ] | <binary set function> [ <filter clause> ] | <ordered set function> [ <filter clause> ] | <array aggregate function> [ <filter clause> ] <filter clause> ::= FILTER <left paren> WHERE <search condition> <right paren>
For example, the following query sums the salary of all employees and female employees in a particular department:
SELECT deptno,
SUM(sal) AS all_sal,
SUM(sal) FILTER ( WHERE gender = 'F' ) AS female_sal
FROM emp
GROUP BY deptno
You could previously achieve this using SUM(CASE WHEN gender = 'F' THEN sal ELSE null END) but that trick only worked on certain aggregate functions.
- relates to
-
CALCITE-703 Obsolete AggregateCall.name
-
- Open
-
-
KYLIN-899 Support aggregate(col) FILTER (WHERE condition)
-
- Closed
-