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:02:56 UTC
[jira] [Created] (CALCITE-704) FILTER clause for aggregate
functions
Julian Hyde created CALCITE-704:
-----------------------------------
Summary: 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)