How to compare the values of a column with the average of these values in SQL?

Question

I have a column of books, and I just want to show the books that the price is higher than the average of those prices, how to do that? The name of my column is price, and I can’t put a where price > avg(price), neither a having price > avg(price).

Answer 1

You can calculate the average before and assign to a variable:

DECLARE @media numeric(15, 2);

SELECT @media = AVG(l.price)
  FROM livros l;

SELECT l.*
  FROM livros l
 WHERE l.price > @media;

Or perform a CROSS JOIN with the calculated value:

SELECT l.*
  FROM livros l
       CROSS JOIN (SELECT AVG(l2.price) AS media
                     FROM livros l2) m
 WHERE l.price > m.media;

A third way is to calculate the average with the WITH:

WITH media AS (
  SELECT AVG(l2.price) AS media
    FROM livros l2
)
SELECT l.*
  FROM livros l
 WHERE l.price > (SELECT m.media
                    FROM media m);