SELECT query with referenced subqueries

Question

How does one reference the currently-grouped by field in a subquery?

Consider a table with the following data:

Client    Status    Size    
   1       A         500
   1       B        1500
   2       A        2000
   2       B         800

I am trying to get the following data in a single query:

Client     A      B
   1      500   1500
   2     2000    800

I've been playing with variations on this, with absolutely no success:

SELECT
  t.Client,
  (
    SELECT A FROM table WHERE Client=t.Client
  ) as A,
  (
    SELECT B FROM table WHERE Client=t.Client
  ) as B
FROM table t
GROUP BY t.Client;

The status for each Client is not repeated, that is, each client should have only one row with a given status. For purpose of this question, we can assume that there is exactly one entry for each status.

Note that there are about one hundred thousand rows: about 20 thousand Clients and five status per client. There exist other fields as well (such as date of status update and the user who performed the update) so changing the table schema to simply Client, A, B is not feasible.

How can I reference the grouped field in the subqueries?

Answer 1

There you go:

SELECT
    Client,
    SUM(IF(Status = "A", Size, 0)) AS A,
    SUM(IF(Status = "B", Size, 0)) AS B
FROM yourtable
GROUP BY Client

Answer 2

This works, there may be something more elegant:

 insert 
 select 1 as client, 'A' as [status], 500 as 'Size'
 into #temp
 insert into #temp
 select 1 as client, 'B' as [status], 1500 as 'Size'
  insert into #temp
 select 2 as client, 'A' as [status], 2000 as 'Size'
 insert into #temp
 select 2 as client, 'B' as [status], 800 as 'Size'

 select t.client, ta.size as 'A', tb.size as 'B'
 from #temp as t
 INNER JOIN (select client, sum(size) as size from #temp where status = 'A' group by client) as ta on t.client = ta.client
 INNER JOIN (select client, sum(size) as size from #temp where status = 'B' group by client) as tb on t.client = tb.client
 group by t.client, ta.size, tb.size