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

          Activity

          Hide
          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).
          Show
          julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/f5434a49 .
          Hide
          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.

            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