[jira] [Resolved] (CALCITE-704) FILTER clause for aggregate functions

["Julian Hyde (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-704?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Hyde resolved CALCITE-704.
---------------------------------
       Resolution: Fixed
    Fix Version/s: 1.3.0-incubating

Fixed in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/f5434a49.

> 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
>             Fix For: 1.3.0-incubating
>
>
> 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)