?
Solved

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

Posted on 2014-04-05
4
Medium Priority
?
1,035 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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month14 days, 16 hours left to enroll

771 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