Search code examples
javamysqlunit-testingjunithsqldb

Executing group by DB query in JUNIT

I have database query similar to below one, it is working fine when i execute this in mySql but it failing in Junit with error "expression not in aggregate or GROUP BY columns:". My JUnit uses in memory HSQL DB. I have gone through the Strange behavior of HSQLDB and group by and understand we need to give group by for all fields when aggregate method is used in the query.

But i have a requirement where i need to get the all the value based on grouping with only one column(which is not primary key), can you please suggest how can i achieve this in JUnit.

Query which I'm executing : Select *, count(sampleField) from TestTable where sampleField2 != null group by sampleField

Solution

  • You can use min(column_name) or max(column_name) for the other columns.

    For example, if you have columns named firstname and lastname

    Select min(firstname), min(lastname), count(sampleField) from TestTable where sampleField2 is not null group by sampleField

    Edited: use is not null instead of != null for correct results.