0

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?

0

2 Answers 2

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
1
  • I had to change the columns to this to make that work: SUM(CASE WHEN [Status] = 'B' THEN SIZE ELSE 0 END) AS B
    – Paul
    Jul 3, 2014 at 10:38
1

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
4
  • Thank you. Though this solution is completely infeasible, +1 for demonstrating that at least I'm not missing something simple!
    – dotancohen
    Jul 3, 2014 at 10:19
  • Cool. How is it unsuitable?
    – Paul
    Jul 3, 2014 at 10:30
  • I would need to script it to run for each status individually (more than the two shown above, OP mentions that) and at the table sizes that we are talking about (100,000 rows, OP mentions that) a temp table is not feasible.
    – dotancohen
    Jul 3, 2014 at 10:37
  • 1
    The temp table is just a representation of your example data to allow the solution shown to be run. It's not part of the solution. I could swear the OP said there were only 5 status types.
    – Paul
    Jul 3, 2014 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.