Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-704

FILTER clause for aggregate functions

        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

            Bug - A problem which impairs or prevents the functions of the product. CALCITE-703 Obsolete AggregateCall.name

            • Major - Major loss of function.
            • Open

            Wish - General wishlist item. KYLIN-899 Support aggregate(col) FILTER (WHERE condition)

            • Major - Major loss of function.
            • Open

              Activity

              Ascending order - Click to sort in descending order
              Hide
              Permalink
              julianhyde Julian Hyde added a comment - - edited

              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.

              Show
              julianhyde Julian Hyde added a comment - - edited 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.
              Hide
              Permalink
              julianhyde Julian Hyde added a comment -

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

              Show
              julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/f5434a49 .
              Hide
              Permalink
              julianhyde Julian Hyde added a comment -

              Resolved in release 1.3.0-incubating (2015-05-30).

              Show
              julianhyde Julian Hyde added a comment - Resolved in release 1.3.0-incubating (2015-05-30).

                People

                • Assignee:
                  julianhyde Julian Hyde
                  Reporter:
                  julianhyde Julian Hyde
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  1 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Development