Solved

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

Posted on 2014-04-05
4
1,025 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 49

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

705 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