Hi, I think the definition of "count" is "number of rows where the expression is not null". Now, both "true" and "false" are not null, so count will not work for what you want. Instead, I suggest to use:
select sum(case when id > 1 then 1 else 0 end) from test; I tested this with H2, PostgreSQL, and MySQL. Regards, Thomas On Wed, Jun 19, 2013 at 12:21 PM, Uli <[email protected]> wrote: > Hi, > > after reading the documentation about the COUNT aggregate function ( > http://www.h2database.com/**html/functions.html#count<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:~/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. > > > -- 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.
