Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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. …
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.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

604 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