apps-infra

    Forum Stats

  • 3,874,064 Users
  • 2,266,670 Discussions
  • 7,911,712 Comments

Discussions

Filter clause for aggregate functions

Martin Preiss
Martin Preiss Member Posts: 2,381 Gold Trophy
edited May 28, 2016 12:56PM in Database Ideas - Ideas

Having written expressions of the form "sum( case when ... then 1 else 0 end )" a million times I would appreciate the introduction of the SQL:2003 Filter clause - as it has been introduced in recent postgres releases:

sum(...) filter (where ...)

Not a very important change, but I assume this should be a quite simple task - being merely syntactic sugar - and it would brings us closer to SQL:2003 and make this kind of aggregations more simple to understand.

Furthermore I would appreciate the introduction of a decent search function here - so I could be sure that this proposal has not already been made...

A concise explanantion of the filter clause in SQL:2003 can be found in Markus Winand's article: filter — Selective Aggregates

Martin PreissulohmannmarkmevansManish ChaturvediSven W.rohanwaliaPravin TakpirePrasad.Gadiraju-OracleCarsten KaftanZlatko SiroticctriebJitendraArpit Jain -OracleberxLothar Flatzfac586Kiran Pawarvinaykumar2bhagatsinghAish13William RobertsonGeert GruwezPkChris HuntBPeaslandDBAuser12182396gaverillkulikouskiChristian ErlingerAndreas BuckenhoferKevan GellingGregVRichard SmithApexBineangelo.stramieriUser_FLNLBsensoftAparna Dutta-OracleLukas EderThomas Teske-OracleChristian Neumueller-OracleGbenga AjakayeNikolaus ThielBeGinThorsten KettnerNiels HeckerDanilo Piazzalungablessed DBARacer I.Billy VerreynneLoïc Lefèvre-OracleGerald Venzl-Oraclephilippe_eUser_9GEWMUser_930JVUser_G3UV3
62 votes

Active · Last Updated

Comments

  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    there are cases where this makes sense

    ApexBine
  • I assume the "..." predicate goes into "where" clause of the proposed syntax. What is supposed to be the "..." argument to the sum? 

    I don't actually see how the proposed syntax makes user typing less. Modern tools (like SQLDeveloper) allow code completion with snippets. As soon as you start typing "case" it would suggest complete sample case expression for you.

    Gbenga Ajakaye
  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    I assume the "..." predicate goes into "where" clause of the proposed syntax. What is supposed to be the "..." argument to the sum? 

    I don't actually see how the proposed syntax makes user typing less. Modern tools (like SQLDeveloper) allow code completion with snippets. As soon as you start typing "case" it would suggest complete sample case expression for you.

    in the example with "then 1 else 0" it would perhaps rather be a partial count - something like "count(*) where column_name = condition". But a partial sum could be also useful in other situations.

    I agree that modern tools makes writing such case constructs quite simple, but I don't think the syntax is extremely intuitive. And so I would prefer an explicit filter. At least the option has made it into the SQL 2003 standard and I think it is always a good idea to support standards (if they are somehow reasonable). Hubert Lubazewski shows an example for the use in postgres 9.4 in select * from depesz; » Blog Archive » Waiting for 9.4 – Implement the FILTER clause for aggregate function calls..

    Thorsten Kettner
  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    in the example with "then 1 else 0" it would perhaps rather be a partial count - something like "count(*) where column_name = condition". But a partial sum could be also useful in other situations.

    I agree that modern tools makes writing such case constructs quite simple, but I don't think the syntax is extremely intuitive. And so I would prefer an explicit filter. At least the option has made it into the SQL 2003 standard and I think it is always a good idea to support standards (if they are somehow reasonable). Hubert Lubazewski shows an example for the use in postgres 9.4 in select * from depesz; » Blog Archive » Waiting for 9.4 – Implement the FILTER clause for aggregate function calls..

    You could always use docode or case and return null if you want to skip a row. However it is nicer with the filter clause. more explicit indeed.

    blessed DBA
  • Racer I.
    Racer I. Member Posts: 113 Green Ribbon
    edited Feb 14, 2019 9:17AM

    Maybe similarly to this :

    select COUNT(c(-1)) negCount, COUNT(c(0)) nullCount, COUNT(c(1)) posCount

    from dual CATEGORY c AS (CASE WHEN dummy < 0 THEN -1 WHEN dummy > 0 THEN 1 ELSE 0 END)

    select SUM(c(-1).Amount) negSum, SUM(c(0).Amount) nullSum, SUM(c(1).Amount) posSum

    from dual CATEGORY c AS (CASE WHEN dummy < 0 THEN -1 WHEN dummy > 0 THEN 1 ELSE 0 END)

    select COUNT(c) posCount, COUNT(NOT c) notPosCount

    from dual CATEGORY c AS (WHERE dummy > 0)

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    Maybe similarly to this :

    select COUNT(c(-1)) negCount, COUNT(c(0)) nullCount, COUNT(c(1)) posCount

    from dual CATEGORY c AS (CASE WHEN dummy < 0 THEN -1 WHEN dummy > 0 THEN 1 ELSE 0 END)

    select SUM(c(-1).Amount) negSum, SUM(c(0).Amount) nullSum, SUM(c(1).Amount) posSum

    from dual CATEGORY c AS (CASE WHEN dummy < 0 THEN -1 WHEN dummy > 0 THEN 1 ELSE 0 END)

    select COUNT(c) posCount, COUNT(NOT c) notPosCount

    from dual CATEGORY c AS (WHERE dummy > 0)

    No, that is a totally different idea.

  • Racer I.
    Racer I. Member Posts: 113 Green Ribbon
    edited Feb 15, 2019 8:55AM

    No, that is a totally different idea.

    Probably next to impossible but maybe all these (and more) can be smooshed together somehow?

  • Thorsten Kettner
    Thorsten Kettner Member Posts: 44 Blue Ribbon

    It's a shame that this is still not available in Oracle 19c. As has been mentioned, this is mainly syntactic sugar, but should also be easy to implement. So far we see different ways to write conditional aggreagtion simply for the lack of that filter clause:

      sum(case when code = 'GB' then 1 else 0 end) as count_gb
      sum(case when code = 'GB' then 1 end) as count_gb
      coalesce(sum(case when code = 'GB' then 1 end), 0) as count_gb
      count(case when code = 'GB' then 1 end) as count_gb
      count(case when code = 'GB' then 'count me' end) as count_gb
      count(case when code = 'GB' then 1 else null end) as count_gb
      count(case when code = 'GB' then 1 else 0 end) as count_gb

    with the last one being a typical mistake, the second one returning null instead of zero and lines one and four being the most typical. It would be way more readable to say we want to count rows, i.e. COUNT(*), where a certain condition is met:

      count(*) filter (where code = 'GB') as count_gb

    philippe_e
  • User_MW1JP
    User_MW1JP Member Posts: 1 Green Ribbon

    Seria realmente válido utilizar essa sintaxe para fornecer mais clareza ao que está sendo validado.

    Internamente é possível até que esteja implementando uma espécie de CASE para chegar a esse mesmo resultado.

apps-infra