Single-row subquery returns more than one row BUT with one row

“CityJail” schema. I am supposed to find each appeal that has a less than the average number of days between the filing and hearing dates.

Here is my code:

SELECT appeal_ID
FROM appeals
WHERE (hearing_date - filing_date) <
(SELECT AVG(hearing_date - filing_date)
FROM appeals
GROUP BY appeal_ID)
GROUP BY appeal_ID;

I get an error:

ORA-01427: single-row subquery returns more than one row 01427. 00000 – “single-row subquery returns more than one row” *Cause:
*Action:

Finding the average works fine by itself:

SELECT AVG(hearing_date - filing_date) "Average"
FROM appeals;

2810.666666666666666666666666666666666667

So I am thinking: it doesn’t like my “GROUP BY” and so I start commenting them out one by one:

SELECT appeal_ID
FROM appeals
WHERE (hearing_date - filing_date) <
(SELECT AVG(hearing_date - filing_date)
FROM appeals;
--GROUP BY appeal_ID)
--GROUP BY appeal_ID;

And now I am getting the error:

ORA-00920: invalid relational operator 00920. 00000 – “invalid relational operator” *Cause:
*Action: Error at Line: 4 Column: 2

The appeals table:

Name         Null?    Type      
------------ -------- --------- 
APPEAL_ID    NOT NULL NUMBER(5) 
CRIME_ID     NOT NULL NUMBER(9) 
FILING_DATE           DATE      
HEARING_DATE          DATE      
STATUS                CHAR(1)

Answer

I don’t think you need any GROUP BY clause:

SELECT appeal_ID
FROM appeals
WHERE hearing_date - filing_date < (SELECT AVG(hearing_date - filing_date) FROM appeals)

The subquery computes the average difference between the two dates over the whole table – don’t use a GROUP BY clause here, otherwise it will return multiple rows, and generate that error. The outer query likely does not need aggregation either.

You could also express this with window functions:

select *
from (select a.*, avg(hearing_date - filing_date) over() avg_diff from appeals a) a
where hearing_date - filing_date < avg_diff