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.
below are snapshot of query result in mysql if using GROUP BY & without GROUP BY: