SQL statement about average

My question is -> Retrieve the segment ID and length of each segment that is longer than the average length of all segments. Name the column indicating the length of segments “Length”.

Relations:

Highway(code, name, startNodeId,endNodeId)

Segment (code, segId, fromKM, toKM) 

City(ZIP,Name)

Node (nodeID, longitude, latitude, type) 

Exit(nodeId, exitNo, ZIP) 

Intersection(nodeId,name) 

HighwayIntersection(code, nodeId,atKm) 

HighwayExit(code, nodeId, atKm) 

What I got so far is:

SELECT code, segid, fromkm, tokm, tokm - fromkm AS Length    
from segment    
GROUP BY segid, tokm-fromkm    
HAVING  AVG( tokm - fromkm) < tokm - fromkm 

And I got this error message:

Error: Your query has syntax errors. Description: java.sql.SQLException: ORA-00979: not a GROUP BY expression

Can anyone say what’s exactly wrong ?

Answer

You can use window functions to achieve this:

SELECT code, segid, fromkm, tokm, length
FROM
    (
        SELECT code, segid, fromkm, tokm, tokm - fromkm AS Length, AVG(tokm - fromkm) OVER () as AverageLength   
        from segment 
    ) seg
WHERE AverageLength < Length