0

I have a situation where I'm constructing a derived pivot table and I'd like to be able to then do subselects against it based on various criteria (in effect making a pivot-pivot table).

So... in pseudo it would look something like this...

select
   (select sum(score) from derivedTable where username like 'a%') as scoresForANames,
   (select sum(score) from derivedTable where username like 'b%') as scoresForBNames

from
  ( select username, score from gameTable where gameId = 111) derivedTable

Note that this is an absurdly simplified example to illustrate what I'm after... I don't need THIS example solved at all... I just need to understand if there's a conceptual way in mySQL to achieve the same result.

The issue is that derivedTable isn't visible to subselects in the outer select. So, I'm curious how I can achieve the same result, or if I'm stuck having to write subselects that take all the criteria into consideration individually.

Yevgeny Simkin
  • 27,946
  • 39
  • 137
  • 236

1 Answers1

1

The way you want to phrase the query is to have no subqueries at all in the select clause:

select sum(case when username like 'a%' then score end) as scoresForANames,
       sum(case when username like 'b%' then score end) as scoresForBNames
from (select username, score
      from gameTable
      where gameId = 111
     ) derivedTable;

Of course, in this case, you don't need the derived table at all:

select sum(case when username like 'a%' then score end) as scoresForANames,
       sum(case when username like 'b%' then score end) as scoresForBNames
from gameTable gt
where gameId = 111;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • ah HA! the key is being able to use the case statement without a subselect! That's exactly what I wasn't clear on and precisely the key to my problem. Can I use the case statement outside of sum? Or does it need to be wrapped in some method? (I can always use some harmless concat or some such if it needs a method wrapper). – Yevgeny Simkin Jul 13 '13 at 19:07