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
import java.sql.*;
import java.util.concurrent.ThreadLocalRandom;
public class CountTest
{
public static void testCount()
throws ClassNotFoundException, SQLException
{
Class.forName("org.h2.Driver");
Connection conn =
DriverManager.getConnection("jdbc:h2:~/gematronik/test/db", "test",
"test");
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE TEST ( t1 BIGINT NOT NULL, t2 BIGINT NOT
NULL)");
PreparedStatement pStmt = conn.prepareStatement("INSERT INTO TEST
(t1, t2) VALUES (?, ?)");
long t = 100000;
for (int i = 0; i < 100; i++)
{
long t2 = ThreadLocalRandom.current().nextLong(900, 1100);
pStmt.setLong(1, t);
pStmt.setLong(2, t + t2);
pStmt.execute();
t += t2;
}
ResultSet rs = stmt
.executeQuery("SELECT COUNT((t2-t1) > 1000), COUNT((t2-t1) <=
1000), COUNT(*) FROM TEST");
rs.next();
int lc = rs.getInt(1);
int sc = rs.getInt(2);
int count = rs.getInt(3);
rs.close();
System.out.format("Count: %3d, Below 1000: %3d, Greater 1000:
%3d\n", count, lc, sc);
System.out.format("(Below 1000 + Greater 1000) == Count ? %b\n",
(lc + sc) == count);
stmt.execute("DROP TABLE TEST");
stmt.close();
conn.close();
}
public static void main(String[] args)
throws ClassNotFoundException, SQLException
{
testCount();
}
}
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
