PostgreSQL - Using result of subquery in the top of select statement

Posted on 2014-04-05
Medium Priority
Last Modified: 2014-04-07

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!
Question by:Musiqal
  • 2
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 39980281
why do you need to pre-caluculate it when you can actually caluculate it at the time of sql execution..

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


select population, abs((population-8136454)) as distance from  country ORDER BY distance ASC;

Author Comment

ID: 39980388
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;
LVL 49

Accepted Solution

PortletPaul earned 2000 total points
ID: 39980560
One way is to use a cross join like this
      , ABS((population - cj.avgpop))AS distance
FROM country
            SELECT AVG(population) AS avgpop FROM country
           ) AS cj
ORDER BY country

Open in new window

LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 39980787
Ok.. then you can also use analytical function to get the desired result

select population, avg_population, abs((population-avg_population)) from (SELECT population,avg(population) OVER () as avg_population FROM country);

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question