Is there any way to do a subselect from a derived table?

Question

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.

Answer 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;