CODEWITHSUNDEEP
mysqlsql
randombits
randombits

Reputation: 48460

Writing a CASE statement in SQL to include multiple columns

I have a fairly straight forward query that looks something like the following:

SELECT
  max(case when site_id = 1 then cast(REPLACE(REPLACE(REPLACE(salary, '$',''),' ',''), ',', '') as UNSIGNED) end) fd_salary,
  max(case when site_id = 2 then cast(REPLACE(REPLACE(REPLACE(salary, '$',''),' ',''), ',', '') as UNSIGNED) end) dd_salary,
  max(case when site_id = 3 then cast(REPLACE(REPLACE(REPLACE(salary, '$',''),' ',''), ',', '') as UNSIGNED) end) ss_salary
FROM mytable 
WHERE gamedate = '2014-07-01'
GROUP BY player_id;

This works as intended. I have a salary column in mytable and I'm trying to get all salaries for a player_id into one row when I run my select statement since right now they are stored in their own rows. My question is the following: How do I add one more column to this select statement besides salary? I'd like to be able to do the same for the position column in addition to the salary column. I'm not sure if I can mix it into the existing case statements or if I have to add three more case statement for each player. If I didn't combine it with salary it would probably look like:

SELECT max(case when site_id = 1 then position end) fd_position, ...

etc. Is there a way to combine these into one query?

Upvotes: 1

Views: 717

Answers (3)

Nicholas Carey
Nicholas Carey

Reputation: 74277

Despite its looks, case is a scalar function that evaluates to a single scalar value. Myself, because I hate repetitive typing, I'd do something like this:

select t.player_id ,
       max( case t.site_id when 1 then t.salary   else 0 end ) as fd_salary   ,
       max( case t.site_id when 1 then t.position else 0 end ) as fd_position ,
       max( case t.site_id when 2 then t.salary   else 0 end ) as dd_salary   ,
       max( case t.site_id when 2 then t.position else 0 end ) as dd_position ,
       max( case t.site_id when 3 then t.salary   else 0 end ) as ss_salary   ,
       max( case t.site_id when 3 then t.position else 0 end ) as ss_position
from ( select player_id ,
              site_id   ,
              gamedate  ,
              cast(REPLACE(REPLACE(REPLACE(salary,'$',''),' ',''), ',', '') as UNSIGNED) as salary ,
              cast(position as UNSIGNED) as position
       from mytable
     ) t
where t.gamedate = '2014-07-01'
  and t.side_id between 1 and 3
group by t.player_id

Upvotes: 2

spencer7593
spencer7593

Reputation: 108410

Q: Is there a way to combine these into one query?

A: Each column returned in the SELECT list is a separate expression.

A CASE expression returns a single value.

You would use a separate CASE expression to return the fd_position column, just as your example shows.

(This is a limitation of SQL, not just MySQL.)

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

have you tried this :

 SELECT
max(case when site_id = 1 then cast(REPLACE(REPLACE(REPLACE(salary, '$',''),' ',''), ',', '') as UNSIGNED) end) fd_salary,
max(case when site_id = 2 then cast(REPLACE(REPLACE(REPLACE(salary, '$',''),' ',''), ',', '') as UNSIGNED) end) dd_salary,
max(case when site_id = 3 then cast(REPLACE(REPLACE(REPLACE(salary, '$',''),' ',''), ',', '') as UNSIGNED) end) ss_salary,
max(case when site_id = 1 then position end) fd_position,
max(case when site_id = 2 then position end) dd_position,
max(case when site_id = 3 then position end) ss_position
from ....

Upvotes: 0

Related Questions