select from union result in postgres

Question

I am trying to query the following:

select currency, sum(volume)
from
    (
        select "baseCurrency" as currency,
               COALESCE("baseVolume", 0) as volume
        from "Pairs"
    )  as a
    union all
    (
        select "quoteCurrency" as currency,
               COALESCE("quoteVolume", 0) as volume
        from "Pairs"
    ) as b
group by currency

But keeps getting errors no matter how I changed the query. For example:

ERROR:  syntax error at or near "as"
LINE 11:  ) as b

What is wrong in this query?

Gordon Linoff · Accepted Answer · 2018-12-02 21:44:08Z

2

Don't use aliases for the subqueries in a union all:

select currency, sum(volume)
from ((select "baseCurrency" as currency, COALESCE("baseVolume", 0) as volume
       from "Pairs"
       ) union all
       (select "quoteCurrency" as currency, COALESCE("quoteVolume", 0) as volume
        from "Pairs"
       ) 
      ) c
group by currency;

In more recent versions of Postgres, you can use a lateral join instead:

select v.currency, sum(v.volume)
from "Pairs" p cross join lateral
     (values ("baseCurrency", "baseVolume"),
             ("quoteCurrency", "quoteVolume")
     ) v(currency, volume)
group by v.currency;

The coalesce() is probably unnecessary. If you are getting NULL results, you can handle it after the sum() . . . coalesce(sum(volume), 0).

answered Dec 2, 2018 at 21:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Naor Over a year ago

Regarding your last comment, putting coalesce out of the sum will make a problem since 3443+null equals null.

Gordon Linoff Over a year ago

@Naor . . . No. Aggregation functions ignore NULL values.

Mihai Chelaru · Accepted Answer · 2018-12-03 01:31:23Z

0

It is according to ISO standards. PostgreSQL was designed to have aliases when subqueries are used. Go here for further information.

In your case the following change will help:

select b.currency, b.sum(volume)
from
  (  (
        select "baseCurrency" as currency, COALESCE("baseVolume", 0) as volume
        from "Pairs"
    )  
    union all
    (
        select "quoteCurrency" as currency, COALESCE("quoteVolume", 0) as volume
        from "Pairs"
    ) )as b
group by currency

edited Dec 3, 2018 at 1:31

Mihai Chelaru

8,47215 gold badges49 silver badges53 bronze badges

answered Dec 2, 2018 at 21:54

Suresh Kumar Tathari

281 silver badge5 bronze badges

Collectives™ on Stack Overflow

select from union result in postgres

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related