CODEWITHSUNDEEP
sqloracle-databasegroup-by
Lyaso
Lyaso

Reputation: 155

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 ?

Upvotes: 0

Views: 61

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

A correlated subquery seems like a reasonable approach:

select s.*
from segment s
where s.length > (select avg(s2.length) from segment s2);

I think this will be a wee bit faster than using window functions, because there is no need to "reassign" the average value on each row. Both require at least two scans through the data (I'm assuming an index would not be helpful because about half the rows are likely to be returned).

Upvotes: 2

JNevill
JNevill

Reputation: 50034

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

Upvotes: 2

Related Questions