Musiqal
asked on
PostgreSQL - Using result of subquery in the top of select statement
Hi,
I have a query i'm working that uses constant "8136454" value from the same table which is the average population of that table.
What i would like to know is if i can pre-calculate the average dynamically in a subquery or any other way and then put it inside of the select-statement, with the condition of not using temporary tables or PL/PgSQL.
Query with constant value:
SELECT *,ABS((population - 8136454))AS distance FROM country ORDER BY distance ASC;
Funtion that i used to calculate constant value "8136454":
SELECT AVG(population) FROM country;
Also if anyone has any good links on how to implement subqueries in various ways in PostgreSQL that would be thankful, as i'm finding it hard wrap my head around this concept.
Thanks guys!
I have a query i'm working that uses constant "8136454" value from the same table which is the average population of that table.
What i would like to know is if i can pre-calculate the average dynamically in a subquery or any other way and then put it inside of the select-statement, with the condition of not using temporary tables or PL/PgSQL.
Query with constant value:
SELECT *,ABS((population - 8136454))AS distance FROM country ORDER BY distance ASC;
Funtion that i used to calculate constant value "8136454":
SELECT AVG(population) FROM country;
Also if anyone has any good links on how to implement subqueries in various ways in PostgreSQL that would be thankful, as i'm finding it hard wrap my head around this concept.
Thanks guys!
ASKER
Yes, but the number 8136454 shouldn't be hard coded, i want to get it dynamically since the average might change of course.
So the number 8136454 actually is the average number of the population field, which got through executing following query seperatly and taking note of number:
SELECT AVG(population) FROM country;
But what i want is that number to be dynamically generated in query below, instead of hard coding it with the added condition of not using any temporary tables or PL/PgSQL:
SELECT *,ABS((population - 8136454))AS distance FROM country ORDER BY distance ASC;
So the number 8136454 actually is the average number of the population field, which got through executing following query seperatly and taking note of number:
SELECT AVG(population) FROM country;
But what i want is that number to be dynamically generated in query below, instead of hard coding it with the added condition of not using any temporary tables or PL/PgSQL:
SELECT *,ABS((population - 8136454))AS distance FROM country ORDER BY distance ASC;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ok.. then you can also use analytical function to get the desired result
select population, avg_population, abs((population-avg_popula tion)) from (SELECT population,avg(population) OVER () as avg_population FROM country);
select population, avg_population, abs((population-avg_popula
SELECT *,ABS((population - 8136454))AS distance FROM country ORDER BY distance ASC;
i think the above query may not be giving you the result because of the wild card *
use column names instead of *, you will get the desired result
like
select population, abs((population-8136454)) as distance from country ORDER BY distance ASC;