You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2015/04/28 02:10:06 UTC
[jira] [Comment Edited] (CALCITE-704) FILTER clause for aggregate
functions
[ https://issues.apache.org/jira/browse/CALCITE-704?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14515988#comment-14515988 ]
Julian Hyde edited comment on CALCITE-704 at 4/28/15 12:09 AM:
---------------------------------------------------------------
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.
was (Author: julianhyde):
Both issues would 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.
> FILTER clause for aggregate functions
> -------------------------------------
>
> Key: CALCITE-704
> URL: https://issues.apache.org/jira/browse/CALCITE-704
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Julian Hyde
>
> The SQL standard provides a means to control which values are accumulated by an aggregate function.
> {code}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>{code}
> For example, the following query sums the salary of all employees and female employees in a particular department:
> {code}SELECT deptno,
> SUM(sal) AS all_sal,
> SUM(sal) FILTER ( WHERE gender = 'F' ) AS female_sal
> FROM emp
> GROUP BY deptno{code}
> 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.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)