Joshua Joshua - 3 months ago 16
Java Question

MySQLSyntaxErrorException when using COUNT with DISTINCT

I get an error with the below sql statement. The sites that I have visited advocate this as the correct way to use these 2 keywords together. I am trying to get the number of unique traits in the results from a test. Name is the PK for the test.

public int getTestCount(Database db, String name) throws SQLException {

int count = 0;
String sql = "SELECT COUNT (DISTINCT Trait) As Count FROM RESULTS WHERE name = ?";

PreparedStatement ps = db.con.prepareStatement(sql);
ps.setString(1, name);

ResultSet rs = ps.executeQuery();

if (rs.next()) {
count = rs.getInt("Count");
}
return count;
}

Answer

Remove the space after the word COUNT

SELECT COUNT(DISTINCT Trait) As Count FROM RESULTS WHERE name = ?

Here is a live example: SQL Fiddle

Comments