SQL Postgres using avg column as variable in outer query

Question

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?

hgmnz · Accepted Answer · 2014-08-02 16:01:52Z

1

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)

answered Aug 2, 2014 at 16:01

hgmnz

13.3k4 gold badges39 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-08-02 15:52:57Z

1

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.

answered Aug 2, 2014 at 15:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL Postgres using avg column as variable in outer query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related