Postgres how to avoid repetitive if then in select statement

Question

What's the easiest way to combine a few columns of a table to set up variables for another query?

I want to avoid constantly repeating the same if then statements in my query

SELECT 
CASE
WHEN team_id = away_team_id THEN away_score * 5
WHEN team_id = home_team_id THEN home_score * 5
END AS team_score_times_five, 
CASE
WHEN team_id = away_team_id THEN away_score - 5
WHEN team_id = home_team_id THEN home_score - 5
END AS team_score_minus_five
FROM t1

Instead I would rather set up one variable

CASE
WHEN team_id = away_team_id THEN away_score
WHEN team_id = home_team_id THEN home_score
END AS team_score

and then query it much more cleanly

SELECT team_score * 5 AS team_score_times_five, team_score - 5 AS team_score_minus_five FROM t1

That should be possible with SELECT-FROM-SELECT but I forget what that feature is called. — melpomene
– melpomene, Commented May 2, 2018 at 19:58

Adam Bethke · Accepted Answer · 2018-05-02 20:00:56Z

2

You might also try common table expressions:

WITH common_table_expression AS (
    SELECT 
        CASE
          WHEN team_id = away_team_id THEN away_score
          WHEN team_id = home_team_id THEN home_score
          END AS team_score
    FROM t1
)
SELECT 
      team_score * 5 AS team_score_times_five
    , team_score - 5 AS team_score_minus_five
FROM common_table_expression

CTEs allow you to stack multiple queries that would be nested in ways that tend to be more readable, and more easily modified in the future (depending on the situation) if the business requirement changes.

This article has a great section entitled "Use Common Table Expressions for extremely readable SQL" that makes additional arguments for why you'd want to use a CTE.

edited May 2, 2018 at 20:00

answered May 2, 2018 at 19:59

Adam Bethke

1,0382 gold badges20 silver badges36 bronze badges

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

Comments

Radim Bača · Accepted Answer · 2018-05-02 19:59:06Z

2

Simply use a subquery behind FROM

 SELECT team_score * 5 AS team_score_times_five, 
        team_score - 5 AS team_score_minus_five
 FROM (
   SELECT CASE
     WHEN team_id = away_team_id THEN away_score
     ELSE home_score END AS team_score
   FROM t1
 ) t

answered May 2, 2018 at 19:59

Radim Bača

10.7k1 gold badge21 silver badges35 bronze badges

Collectives™ on Stack Overflow

Postgres how to avoid repetitive if then in select statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related