• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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..
 
Bartender
Posts: 1051
5
Hibernate Eclipse IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1051
5
Hibernate Eclipse IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 1051
5
Hibernate Eclipse IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 would challenge you to a battle of wits, but I see you are unarmed - shakespear. Unarmed tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic