1

How do I use the result of a sub query multiple times? Is there any way to name that result and use it somewhere else? I know about with xyz as ... and that doesn't seem to work?

I found this and would like something more specific?

Sample of broken code:

with g_surf as (select surface_area from countries where name like 'Germa%')
select abs(surface_area - g_surf) from countries;

working code that uses the entire sub query:

select abs(surface_area - (select surface_area from
    countries where name like 'Germa%')) from countries;
AturSams
  • 7,568
  • 18
  • 64
  • 98
  • 3
    It is called CTE (common table expression). Maybe if you post your try, we can tell why you didn't manage to do it. – FXD Jan 28 '19 at 11:53
  • Possible duplicate of [PostgreSQL table variable](https://stackoverflow.com/questions/10785767/postgresql-table-variable) – sticky bit Jan 28 '19 at 11:58
  • 1
    You've been a member long enough to know that we're going to need more right? Code, attempts, errors, etc. Also, if you link something, include/quote the important bits so when the link dies, the question doesn't. - edit: and possibly use [pg_fetch_all](http://php.net/manual/en/function.pg-fetch-all.php) and save subquery results as stand-alone query? – rkeet Jan 28 '19 at 12:03
  • 1
    @rkeet Is this ok? – AturSams Jan 28 '19 at 12:16
  • 1
    g_surf is the CTE, i.e. it acts as if it is a table, not a field. Therefore: `with g_surf as (select surface_area from countries where name like 'Germa%') select abs(surface_area) from g_surf;` Of course, if you have a g_surf field in your `countries` table, you can write `with myCTE as (select surface_area, g_surf from countries where name like 'Germa%') select abs(surface_area - g_surf) from myCTE;` – FXD Jan 28 '19 at 12:26

1 Answers1

1

Just to mark the question as solved:
g_surf, in your example, is the CTE (Common Table Expression), i.e. it acts as a table, not as a field. 

with g_surf as (select surface_area from countries where name like 'Germa%')
select abs(surface_area) from g_surf;

Of course, if you have a g_surf field in your countries table, you can write:

with myCTE as (select surface_area, g_surf from countries where name like 'Germa%')
select abs(surface_area - g_surf) from myCTE;

More about CTE here.

FXD
  • 1,960
  • 1
  • 6
  • 9
  • Thanks a ton. I will check this out soon. – AturSams Jan 30 '19 at 05:53
  • Wait, in your first example, there is no subtraction? (so does it really do what I set out to do) Now in your second example, how does surface_area knows it's from countries and g_surf knows its from myCTE? – AturSams Jan 30 '19 at 05:58
  • It is because your broken code was a bit strange ... When you write `with g_surf as (select surface_area from countries where name like 'Germa%')`, you are very roughly saying: Create a temporary table called `g_surf` whose only field is `surface_area` (no `g_surf` column). However, the part `select abs(surface_area - g_surf) from countries` suggested `g_surf` is a column identifier in table `countries`. I just did not know what assumption was right. If the second query works, I guess that is what you are looking for. – FXD Jan 30 '19 at 11:02
  • Well, I'll explain then. The "working code" sample is the one I'm aiming for. I want select, order by and also use where on the difference in surface area and I want to write the sub-query only one time. I will check out your code today. Thanks for helping! – AturSams Feb 01 '19 at 10:04