Using a sub-query to list greater than averages
<p>What I have is a table which has the prices of book titles. What I'm trying to do is to list the book titles that have a greater than average price. What I have so far is :</p>

<pre><code>SELECT TITLES.PRICE, AVG(TITLES.PRICE) AS "KOMPARE"
FROM TITLES,
            (SELECT PRICE, AVG(PRICE)
            FROM TITLES
            GROUP BY Price) subquery1

WHERE TITLES.PRICE = subquery1.PRICE
AND subquery1.PRICE &gt; TITLES.KOMPARE
GROUP BY TITLES.PRICE;
</code></pre>

<p>The error that I am getting is that <code>TITLES.KOMPARE</code> is a invalid identifier. I'm not sure why as I defined it in the first select statement. And it's not like I could put <code>AVG(TITLES.PRICE)</code> in the <code>AND</code> statement. I would be forever thankful for anyone who can offer me some advice! Thank you.</p>

<p>FYI my table name is TITLES, and the prices of the books are PRICE. </p>