Using a sub-query to list greater than averages

Question

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 :

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 > TITLES.KOMPARE
GROUP BY TITLES.PRICE;

The error that I am getting is that TITLES.KOMPARE 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 AVG(TITLES.PRICE) in the AND statement. I would be forever thankful for anyone who can offer me some advice! Thank you.

FYI my table name is TITLES, and the prices of the books are PRICE.

50 minutes is nothing, especially if you are new. – PM 77-1 Jul 27 '16 at 19:43 — PM 77-1, Jul 27 '16 at 19:43
Use `HAVING` and it will work – gr1zzly be4r Jul 27 '16 at 19:46 — gr1zzly be4r, Jul 27 '16 at 19:46

ScaisEdge · Answer 1 · 2016-07-27T20:38:12.837

1

for aggregation function you should use having and no alias

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
GROUP BY TITLES.PRICE
HAVING TITLES.PRICE > AVG(TITLES.PRICE)

could be you are looking for somethings like

select  TITLES.PRICE,select AVG(TITLES.PRICE) from TITLES   )
from TITLES   
where TITLES.PRIVE  > (select AVG(TITLES.PRICE) from TITLES   );

edited Jul 27 '16 at 20:38

answered Jul 27 '16 at 19:46

ScaisEdge

131,976
10
91
107

I'm getting an error saying not a GROUP BY Expression – Ashley Johnson Jul 27 '16 at 19:55
Update the answer but you query have no sense .. – ScaisEdge Jul 27 '16 at 19:57
I have update the answe with a my suggestion – ScaisEdge Jul 27 '16 at 20:00
Still **ORA-00979: not a GROUP BY expression Error at Line: 4 Column: 8**. See for yourself: `WITH Titles (BOOKNAME, price) AS ( SELECT 1, 2.00 FROM DUAL UNION ALL SELECT 2, 4.00 FROM DUAL UNION ALL SELECT 4, 5.00 FROM DUAL ) SELECT TITLES.BOOKNAME, AVG(TITLES.PRICE) AS "KOMPARE" FROM TITLES GROUP BY TITLES.BOOKNAME HAVING TITLES.PRICE > AVG(TITLES.PRICE);` – Abecee Jul 27 '16 at 20:32

score 0 · Answer 2 · answered Jul 27 '16 at 19:55

Your query makes no sense, getting the average PRICE while grouping by PRICE?

SELECT TITLE.*
FROM TITLES
CROSS JOIN
  (
    SELECT AVG(PRICE) AS avgPrice
    FROM TITLES
  ) subquery1
WHERE TITLES.PRICE > avgPrice
;

Or better:

SELECT *
FROM TITLES
WHERE TITLES.PRICE >
  (
    SELECT AVG(PRICE) AS avgPrice
    FROM TITLES
  )
;

Abecee · Answer 3 · 2016-07-27T20:09:03.990

You could work along:

SELECT
  price
  , ROUND((SELECT AVG(price) FROM Titles), 2) AS kompare
FROM Titles
WHERE price > (SELECT AVG(price) FROM Titles)
;

You might want to retrieve more columns from Titles - which was not possible the way, you wrote your statement. (But why would you GROUP BY price in the first place?)

You could even have the difference listed:

SELECT
  Titles.*
  , ROUND(price - (SELECT AVG(price) FROM Titles), 2) AS difference
FROM Titles
WHERE price > (SELECT AVG(price) FROM Titles)
;

Please comment if and as this requires adjustment / further detail.

Using a sub-query to list greater than averages

3 Answers3