CODEWITHSUNDEEP
sqlpostgresqlaverage
Yuval Levy
Yuval Levy

Reputation: 2516

SQL Postgres using avg column as variable in outer query

I wrote the following query:

SELECT DISTINCT gno, avg(weight) as x
    FROM Edge
    GROUP BY gno
    HAVING avg(weight)<max(weight)/2

and got this (correct) table: enter image description here

now i want to use the right column and find the max value there and show only that line. how can I do it?

Upvotes: 0

Views: 362

Answers (2)

hgmnz
hgmnz

Reputation: 13306

You can always compose queries like this using CTEs:

WITH averages AS (
  SELECT DISTINCT gno, avg(weight) as x
    FROM Edge
    GROUP BY gno
    HAVING avg(weight)<max(weight)/2
)
SELECT MAX(x) FROM averages;

(but be aware that CTEs are optimization fences)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

If I understand correctly, use order by and limit:

SELECT gno, avg(weight) as x
FROM Edge
GROUP BY gno
HAVING avg(weight)<max(weight)/2
ORDER BY avg(weight) desc
LIMIT 1;

Note that when using group by, you don't need select distinct.

Upvotes: 1

Related Questions