Combine strings to select column

Question

I have a table like this

books
|---------------------|------------------|------------------|------------------|
|         book        |       source     |    store_price   |  bookstore_price |
|---------------------|------------------|------------------|------------------|
|        Batman       |       store      |       10.5       |        9.5       |
|---------------------|------------------|------------------|------------------|
|       Superman      |     bookstore    |       null       |         9        |
|---------------------|------------------|------------------|------------------|

I need to select books and sum prices and my query so far looks like this

SELECT
    book,
    SUM(
        CASE
            WHEN source = 'store' then store_price
            WHEN source = 'bookstore' then bookstore_price
        END
    ) AS price
FROM books
GROUP BY 1
;

Is it possible to somehow optimize SUM select for ex. select source||'_price' from books;?

Thank you in advance!

Change your design. There is no need for 2 price columns. Use 1 column price and then the column source is enough to distinguish what kind of price you have. — forpas
– forpas, Commented Mar 24, 2020 at 7:46
this is short version of a complex table view i have and i can not change it ;) — b00sted 'snail'
– b00sted 'snail', Commented Mar 24, 2020 at 7:49
with t(book, source, store_price, bookstore_price) as (values('Batman'::text, 'store'::text, 10.5, 9.5), ('Superman', 'bookstore', null, 9)) select *, (to_jsonb(t)->(source||'_price'))::numeric from t; But it is really bad design IMO. — Abelisto
– Abelisto, Commented Mar 24, 2020 at 8:46

Gordon Linoff · Accepted Answer · 2020-03-24 11:52:55Z

1

I"m not 100% sure if this helps, but you can unpivot the data and aggregate:

select b.book, sum(v.price)
from books b join lateral
     (values ('store', store_price),
             ('bookstore', bookstore_price)
     ) v(source, price)
     on v.source = b.source
group by b.book;

answered Mar 24, 2020 at 11:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

IVO GELOV · Accepted Answer · 2020-03-24 07:36:47Z

0

You can use the COALESCE function:

SELECT
    book,
    source,
    SUM(
        COALESCE(store_price,bookstore_price,0)
    ) AS price
FROM books
GROUP BY 1
;

answered Mar 24, 2020 at 7:36

IVO GELOV

14.3k1 gold badge21 silver badges30 bronze badges

2 Comments

b00sted 'snail' Over a year ago

it will work only if one column is filled, but won't if both

IVO GELOV Over a year ago

Yes - if you have more than 1 non-NULL price then you have bad data in the database and will have to either use CASE statements to overcome that or to cleanup the data and put a constraint so that only one of the prices is non-NULL.

Collectives™ on Stack Overflow

Combine strings to select column

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related