Executing group by DB query in JUNIT

Question

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

be careful... how are you connecting to the DB? because credentials via JNDI will not be accessable through JUnit... the test will need its own hardcoded credentials. speaks from experience — Jason V
– Jason V, Commented Aug 9, 2017 at 16:06
With group by you can only use grouped columns and aggregates in the selectlist. don't know how this can work in MySQL except for a table with just one column. — aschoerk
– aschoerk, Commented Aug 9, 2017 at 16:23
There is nothing strange about that, it is simply invalid SQL. It seems you've tried to deduce your question down with some psuedo SQL. Please post the full query that worked in MySQL, along with the complete query you're attempting to execute in HQL. — slambeth
– slambeth, Commented Aug 9, 2017 at 18:40

fredt · Accepted Answer · 2020-08-08 14:17:32Z

1

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.

edited Aug 8, 2020 at 14:17

answered Aug 9, 2017 at 19:00

fredt

24.5k3 gold badges43 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Executing group by DB query in JUNIT

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related