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

FILTER clause for aggregate functions

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.3.0-incubating
    • None
    • 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.

      Attachments

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment