Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: 1.3.0-incubating
-
Component/s: None
-
Labels: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.
Issue Links
- relates to
-
CALCITE-703
Obsolete AggregateCall.name
-
- Open
-
-
KYLIN-899
Support aggregate(col) FILTER (WHERE condition)
-
- Open
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Show
Julian Hyde
added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/f5434a49 .
Resolved in release 1.3.0-incubating (2015-05-30).
Show
Julian Hyde
added a comment - Resolved in release 1.3.0-incubating (2015-05-30).
This and CALCITE-703 both require changes to AggregateCall constructor. To minimize the number of deprecated APIs we create, would be good to do them in the same minor release.