Solved

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

Posted on 2014-04-05
4
1,012 Views
Last Modified: 2014-04-07
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!
0
Comment
Question by:Musiqal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
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

like

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

Author Comment

by:Musiqal
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;
0
 
LVL 48

Accepted Solution

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

Open in new window

0
 
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);
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Filtering characters in an SQL field 2 21
Help with Oracle IF statment 5 36
SQL Server syntax 11 41
How to backup in postgresql 8 27
Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.

726 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