title: Single-row subquery returns more than one row BUT with one row
body: <p>&quot;CityJail&quot; schema. I am supposed to find each appeal that has a less than the average number of days between the filing and hearing dates.</p>
  <p>Here is my code:</p>
  <pre><code>SELECT appeal_ID
  FROM appeals
  WHERE (hearing_date - filing_date) &lt;
  (SELECT AVG(hearing_date - filing_date)
  FROM appeals
  GROUP BY appeal_ID)
  GROUP BY appeal_ID;
  </code></pre>
  <p>I get an error:</p>
  <blockquote>
  <p>ORA-01427: single-row subquery returns more than one row
  01427. 00000 -  &quot;single-row subquery returns more than one row&quot;
  *Cause:<br />
  *Action:</p>
  </blockquote>
  <p>Finding the average works fine by itself:</p>
  <pre><code>SELECT AVG(hearing_date - filing_date) &quot;Average&quot;
  FROM appeals;
  </code></pre>
  <blockquote>
  <p>2810.666666666666666666666666666666666667</p>
  </blockquote>
  <p>So I am thinking: it doesn't like my &quot;GROUP BY&quot; and so I start commenting them out one by one:</p>
  <pre><code>SELECT appeal_ID
  FROM appeals
  WHERE (hearing_date - filing_date) &lt;
  (SELECT AVG(hearing_date - filing_date)
  FROM appeals;
  --GROUP BY appeal_ID)
  --GROUP BY appeal_ID;
  </code></pre>
  <p>And now I am getting the error:</p>
  <blockquote>
  <p>ORA-00920: invalid relational operator
  00920. 00000 -  &quot;invalid relational operator&quot;
  *Cause:<br />
  *Action:
  Error at Line: 4 Column: 2</p>
  </blockquote>
  <p>The appeals table:</p>
  <pre><code>Name         Null?    Type      
  ------------ -------- --------- 
  APPEAL_ID    NOT NULL NUMBER(5) 
  CRIME_ID     NOT NULL NUMBER(9) 
  FILING_DATE           DATE      
  HEARING_DATE          DATE      
  STATUS                CHAR(1)
  </code></pre>