SQLDeveloper runs query but getting a “ORA-00979: Not a Group By expression” from hibernate

I have data that looks something like this:

  sensorid |  sampletime             |  correctedvalue   | qualityflag
-----------------------------------------------------------------------
  4472     | 27-OCT-10 00:00:00.123  |   3.75            | 0
  4472     | 27-OCT-10 00:00:01.324  |   3.85            | 0
  4472     | 27-OCT-10 00:00:02.123  |   3.92            | 0
  4472     | 27-OCT-10 00:00:03.324  |   4.05            | 0

And a query that works fine in Oracle SQL Developer (It returns data averaged over 15 second periods):

select sensorid,
    trunc(sampletime,'hh24') + 
    (trunc(to_char(sampletime,'mi')))/24/60 +
    (trunc(to_char(sampletime,'ss')/15)*15)/24/60/60 as tspan, 
    avg(correctedvalue),
    max(qualityflag)
from scalarsample
group by sensorid, 
    trunc(sampletime,'hh24') + 
    (trunc(to_char(sampletime,'mi')))/24/60 +
    (trunc(to_char(sampletime,'ss')/15)*15)/24/60/60
order by tspan

but when I plug it into my java code, I get an error:

org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
...
Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:221)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:118)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:224)
...

The actual query string I use in the Jave looks more like this:

select sensorid,
    trunc(sampletime,'hh24') + 
    (trunc(to_char(sampletime,'mi')))/24/60 +
    (trunc(to_char(sampletime,'ss')/?)*?)/24/60/60 as tspan, 
    avg(correctedvalue),
    max(qualityflag)
from scalarsample
where sampletime between ? and ?
    and sensorid = ?
group by sensorid, 
    trunc(sampletime,'hh24') + 
    (trunc(to_char(sampletime,'mi')))/24/60 +
    (trunc(to_char(sampletime,'ss')/?)*?)/24/60/60
order by tspan

and the parameters are set by calling:

SQLQuery q = session.createSQLQuery(queryString);
q.setInteger(0, averagingWindowInSeconds);
q.setInteger(1, averagingWindowInSeconds);
q.setTimestamp(2, dateFrom);
q.setTimestamp(3, dateTo);
q.setInteger(4, sensorId);
q.setInteger(5, averagingWindowInSeconds);
q.setInteger(6, averagingWindowInSeconds);
q.addEntity(ScalarSampleState.class);

Anyone know why I’m getting this “Not a GROUP BY expression” error?

I picked up this way of doing averaging over time intervals from Tom here: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4222062043865

EDIT It almost seemed to be resolved by the following:

select sensorid,
    trunc(sampletime,'hh24') + 
    (trunc(to_char(sampletime,'mi')))/24/60 +
    (trunc(to_char(sampletime,'ss')/?)*?)/24/60/60 as sampletime, 
    avg(correctedvalue),
    max(qualityflag)
from scalarsample
where sampletime between ? and ?
    and sensorid = ?
group by sensorid, sampletime
order by sampletime

Note: I used sampletime to rename the column, which is the name of one of the columns in the table. It wouldn’t work with the name “tspan” instead of sampletime:

ORA-00904: "TSPAN": invalid identifier
00904. 00000 -  "%s: invalid identifier"

When I named the new column sampletime, and used sampletime in the GROUP BY by clause, that error went away, and the query ran perfectly in SQLDeveloper. Unfortunately, when running from Java, it returned several identical rows for each sampletime. Grrrr….

SOLUTION: What did work is the chosen solution below — I took out the quotation marks and plus signs around the string to make it more readable here:

SELECT
    sensorid, 
    TRUNC( sampletime, hh24) +
    (TRUNC(to_char(sampletime,'mi')))/24/60 +
    (TRUNC(to_char(sampletime,'ss')//averagingWindowInSeconds )*averagingWindowInSeconds)/24/60/60 as sampletime,
    AVG( correctedvalue) as correctedvalue, 
    MAX(qualityflag)
FROM scalarsample
WHERE 
    sampletime BETWEEN ? AND ?
    AND sensorid = ?
GROUP BY sensorid,
    TRUNC( sampletime, hh24) +
    (TRUNC(to_char(sampletime,'mi')))/24/60 +
    (TRUNC(to_char(sampletime,'ss')//averagingWindowInSeconds 
ORDER BY sampletime ";  

Answer

It looks like the JDBC driver is creating a unique bind variable name for each “?”. Unfortunately, in Oracle, the group by clauses must match character for character with the select clauses, and because of this, it doesn’t. I mocked up a table with your data in Oracle, and I ran a few test queries with dynamic SQL and bind variables. First, with sequentially named bind variables:

SQL> l
  1  declare
  2  cur sys_refcursor;
  3  begin
  4  open cur for 'select sensorid, ' ||
  5  '    trunc(sampletime,''hh24'') +  ' ||
  6  '    (trunc(to_char(sampletime,''mi'')))/24/60 + ' ||
  7  '    (trunc(to_char(sampletime,''ss'')/:b1)*:b2)/24/60/60 as tspan,  ' ||
  8  '    avg(correctedvalue), ' ||
  9  '    max(qualityflag) ' ||
 10  'from scalarsample ' ||
 11  'where sampletime between DATE ''2010-10-27'' and DATE ''2010-10-28'' ' ||
 12  '    and sensorid = 4472 ' ||
 13  'group by sensorid,  ' ||
 14  '    trunc(sampletime,''hh24'') +  ' ||
 15  '    (trunc(to_char(sampletime,''mi'')))/24/60 + ' ||
 16  '    (trunc(to_char(sampletime,''ss'')/:b3)*:b4)/24/60/60 ' ||
 17  'order by tspan'
 18  using 15, 15, 15, 15;
 19  close cur;
 20* end;

The results:

SQL> @test
declare
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
ORA-06512: at line 4

Second, with the same named bind variables:

SQL> l
  1  declare
  2  cur sys_refcursor;
  3  begin
  4  open cur for 'select sensorid, ' ||
  5  '    trunc(sampletime,''hh24'') +  ' ||
  6  '    (trunc(to_char(sampletime,''mi'')))/24/60 + ' ||
  7  '    (trunc(to_char(sampletime,''ss'')/:b1)*:b1)/24/60/60 as tspan,  ' ||
  8  '    avg(correctedvalue), ' ||
  9  '    max(qualityflag) ' ||
 10  'from scalarsample ' ||
 11  'where sampletime between DATE ''2010-10-27'' and DATE ''2010-10-28'' ' ||
 12  '    and sensorid = 4472 ' ||
 13  'group by sensorid,  ' ||
 14  '    trunc(sampletime,''hh24'') +  ' ||
 15  '    (trunc(to_char(sampletime,''mi'')))/24/60 + ' ||
 16  '    (trunc(to_char(sampletime,''ss'')/:b1)*:b1)/24/60/60 ' ||
 17  'order by tspan'
 18  using 15, 15, 15, 15;
 19  close cur;
 20* end;

The results:

SQL> @test
PL/SQL procedure successfully completed.

So, try concatenating your averagingWindowInSeconds value to your query, instead of having those 4 bind variables.