How to use the results of a select in another select in a union query?

Question

I have a union query, where I want to use the results of the select in the "left side" of the union query, in the select statement on the "right side" of the union query. The query below works correctly (on postgres at least), but I am running query1 2 times, once as query1, and again as sameAsQuery1.

select x as zz from (select 69 as x) as query1
union all
select count(zz) as zz from 
      (select x as zz from (select 69 as x) as sameAsQuery1) as query2

I would like to do something like this so I don't have to run query1 2 times, but it doesn't work:

select x as zz from (select 69 as x) as query1
union all
select count(zz) as zz from query1

I get this error message:

ERROR: relation "query1" does not exist LINE 3: select count(zz) as zz from query1

Is there a way to rewrite this query so query1 only runs once?

Minor modification to Mr. Llama's response worked quite well, it looks like this (Note the addition of "as q2"):

WITH
query1 AS
(
    SELECT x AS zz FROM (SELECT 69 AS x) as q2
)
SELECT zz FROM query1
UNION ALL
SELECT COUNT(zz) AS zz FROM query1

@Mr. Llama's response, with a minor modification, worked well. — StvnBrkdll, Sep 24 '15 at 18:53
`select x as zz from (select 69 as x) as query1` can be replaced with a simple `select 69` or just `values (69)` — , Sep 24 '15 at 19:06

score 4 · Accepted Answer · answered Sep 24 '15 at 15:31

You're looking for common table expressions.

They let you define a result and use it multiple times within a query.

In your fist case:

WITH
query1 AS
(
    SELECT x AS zz FROM (SELECT 69 AS x)
)
SELECT zz FROM query1
UNION ALL
SELECT COUNT(zz) AS zz FROM query1

score 1 · Answer 2 · answered Sep 24 '15 at 15:32

1

Why would do something like this when most databases support group by and rollup? It seems you want something like this:

select x, count(*) as cnt
from <whatever>
group by x with rollup;

answered Sep 24 '15 at 15:32

Gordon Linoff

1,242,037
58
646
786

Postgres does not (yet) support rollup (I think it will be in the upcoming 9.5 though) – Sep 24 '15 at 19:03
@a_horse_with_no_name . . . In my defense, it was not tagged Postgres when I answered the question. (And Oracle, MySQL, SQL Server, Teradata, and DB2 -- and probably others too -- do support it or something similar.) – Gordon Linoff Sep 24 '15 at 21:54

score 0 · Answer 3 · answered Sep 24 '15 at 15:31

0

Put the query1 result into temp table

select x as zz from (select 69 as x) as query1 into temptable

Now use the temp table in second query

select zz from temptable
union all
select count(zz) as zz from temptable

answered Sep 24 '15 at 15:31

Pரதீப்

91,748
19
131
172

Using the non-standard `select .. into ..` is discouraged. It's better to use the standard `create table .. as select ..` syntax (plus a temp table isn't necessary in the first place) – Sep 24 '15 at 19:03

score 0 · Answer 4 · answered Sep 24 '15 at 15:32

0

You probably need a Common Table Expression to reuse the SELECT:

with cte as
( select x as zz from (select 69 as x) as query1 )
select * from cte
union all
select count(zz) as zz from cte

answered Sep 24 '15 at 15:32

dnoeth

59,503
4
39
56

How to use the results of a select in another select in a union query?

4 Answers4