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

Question

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

Navigation

#1 by (1 votes)

2

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

sql-server query

asked by anonymous 25.08.2017 / 15:38

1 answer

RelationsShips Larval 5.4 How to generate 5-digit number automatically?

score 1 · Accepted Answer

You can calculate the mean before and assign it 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 make a CROSS JOIN with the computed 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 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);