wood burning stoves 2.0*
The moose likes JDBC and the fly likes HSQL view ERROR: expression not in aggregate or GROUP BY columns Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "HSQL view ERROR: expression not in aggregate or GROUP BY columns" Watch "HSQL view ERROR: expression not in aggregate or GROUP BY columns" New topic
Author

HSQL view ERROR: expression not in aggregate or GROUP BY columns

Sally william
Greenhorn

Joined: Mar 03, 2012
Posts: 15
Hi all,
i have a CREATE VIEW query in hsql, but whenever i run it, it throws me this error:

expression not in aggregate or GROUP BY columns: AGV.ID

I understand that GROUP BY would not work without any aggregate expressions(AVG, SUM, MIN, MAX), but i cant figure out how to fix my query.. because each record need to be grouped by manifestID value.
Basically, im trying to create a VIEW by combining 3 set of select queries.
I tried to use distinct but no luck, since it will not work if i have multiple selected columns.
This query works fine in MYSQL.

Please help...

My Query:


below are snapshot of query result in mysql if using GROUP BY & without GROUP BY:





Thanks in advanced..
James Boswell
Bartender

Joined: Nov 09, 2011
Posts: 1012
    
    5

I don't see the need for your GROUP BY clause as you are not using any aggregate functions (e.g. AVG, MIN, MAX)
Sally william
Greenhorn

Joined: Mar 03, 2012
Posts: 15
Hi James,

I need group by there because my result might return multiple records of same manifestID. So, i need to show it as 1 record only on my page, thus i need to group it.
James Boswell
Bartender

Joined: Nov 09, 2011
Posts: 1012
    
    5

Sally

My understanding of using GROUP BY is that your corresponding SELECT statement must contain an aggregate function.

What error did you get when you used DISTINCT on manifestID?
Sally william
Greenhorn

Joined: Mar 03, 2012
Posts: 15

I didnt get any errors when use DISTINCT without group by, but the result is still having multiple records with same manifestID..
James Boswell
Bartender

Joined: Nov 09, 2011
Posts: 1012
    
    5

Sally

Look at the first two rows of the data when you leave out the GROUP BY clause. They aren't the same row as their values are different.

A UNION will only consider a row as a duplicate if all values in the SELECT clause are the same.
Sally william
Greenhorn

Joined: Mar 03, 2012
Posts: 15
thanks to all...
and sorry for the late reply..
i managed to solve it..
you guys were right..

i removed the GROUP BY clause from the hsql query and during calling the query i used this sql statement:



 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: HSQL view ERROR: expression not in aggregate or GROUP BY columns
 
Similar Threads
SQL Server Exception
Hibernate
SQL Question
Help with an SQL Statement