Question 113910 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Custom row cell in SQL

So I’m looking to use a custom row cell to label my data.

Basketball Baseball Golf Cost
1 0 0 $50
0 1 0 $75
1 0 1 $150
0 1 1 $225

The table I have is above. What I’m trying to do is below:

OUTPUT:

Sport Cost
Basketball 200
Baseball 300
Golf 375

I can get the sum of each sport but I’m having trouble making an alias for each sport on the output table (The first column)

How would I go about that? I’ve done an alias for a column header, but never for a row cell.

Thanks in advance!

Tags:

2

Answers


  1. 0

    Use a UNION with different WHERE conditions:

    select sport, sum(cost)
from 
( 
  select 'Basketball' as sport, cost
  from the_table 
  where basketball = 1
  union all
  select 'Baseball', cost
  from the_table 
  where baseball = 1
  union all
  select 'Golf', cost
  from the_table 
  where golf = 1
) t
group by sport;
    Login or Signup to reply.
  2. 0 
    select   game
        ,sum(cost*flg) as cost
        
from t 
     cross join lateral (
     values 
            (basketball, 'basketball')
           ,(baseball, 'baseball')
           ,(golf, 'golf')
                        ) t2(flg, game)
group by game
    game cost
    golf 375
    baseball 300
    basketball 200

    Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search