Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
Description
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.
Attachments
Issue Links
- relates to
-
CALCITE-703 Obsolete AggregateCall.name
- Open
-
KYLIN-899 Support aggregate(col) FILTER (WHERE condition)
- Closed