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 :
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
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