Re: [h2] Problem with conditional COUNT

[Noel Grandin]

No, sorry, that's not valid SQL at all.

They have to be executed as separate statements.

SELECT COUNT(*) FROM TEST WHERE (t2-t1) > 1000)
SELECT COUNT(*) FROM TEST WHERE (t2-t1) <= 1000)
SELECT COUNT(*) FROM TEST

or like this, if you really needed the efficiency:

SELECT ((t2-t1) > 1000), COUNT(*)
FROM TEST
GROUP BY (t2-t1) > 1000

And then you'd have to add the values from the result set yourself to get the total.

On 2013-06-19 12:18, Uli wrote:

Hi,

after reading the documentation about the COUNT aggregate function (http://www.h2database.com/html/functions.html#count) I would expect that it is possible to have a conditional COUNT.

But it does not seem to work as the test program below shows. It creates a table with two colums t1 and t2. t2 is always > t1. The program should count all rows with t2-t1 > 1000 and t2-t1 <= 1000. But both counts returns the number of all rows so it does not mention the conditions.

Should this be supported in H2?

Thanks!
Uli

[Uli]

Thanks!

For the sake of completeness: after carefully rereading the documentation I found a solution to get the values in one query with one resulting row.
The documentation says that COUNT counts all non-NULL values. When combining the boolean expression with CASEWHEN it is possible to use the COUNT aggregate function:

  SELECT COUNT(CASEWHEN((t2-t1) > 1000, 1, null)),
         COUNT(CASEWHEN((t2-t1) <= 1000, 1, null)),
         COUNT(*)
  FROM TEST

So I think the original query was not really invalid (in this case the SQL parser should have complained) but it did not match how COUNT works.

Uli