hsqldb - Issues when using HSQL and WHERE statement

Question

I have an issue when using a WHERE statement in HSQL. When I enter the code as follows I get no errors:

"SELECT Year, ProjectName, (cast(sum(Missed) as float)/(select cast(sum(d.Missed) as float) from Draft d)) as MissedPer from Draft group by ProjectName, Year order by ProjectName"

However, when I add a WHERE statement inside the nested query I get an error:

"SELECT Year, ProjectName, (cast(sum(Missed) as float)/(select cast(sum(d.Missed) as float) from Draft d where Year = '19.12')) as MissedPer from Draft group by ProjectName, Year order by ProjectName"

The error is the following:

Exception in thread "main" java.sql.SQLSyntaxErrorException: expression not in aggregate or GROUP BY columns: CAST(( SUM(PUBLIC.DRAFT.MISSED)) AS DOUBLE)/(()) in statement [SELECT BranchNo, ProjectName, (cast(sum(Missed) as float)/(select cast(sum(d.Missed) as float) from Draft d where d.BranchNo = '19.12')) as MissedPer from Draft group by ProjectName, BranchNo order by ProjectName]

I don't know how to make this work, please help.

question from:https://stackoverflow.com/questions/65883347/issues-when-using-hsql-and-where-statement

Answer 1

I checked with HSQLDB version 2.5.1 and the query returns a result:

create table draft (year varchar(10), projectname varchar (10), missed int);
insert into draft values '19.10', 'p alpha', 10
insert into draft values '19.11', 'p alpha', 12
insert into draft values '19.12', 'p alpha', 3


YEAR  PROJECTNAME MISSEDPER          
----- ----------- ------------------ 
19.10 p alpha     3.3333333333333335 
19.11 p alpha     4.0                
19.12 p alpha     1.0