Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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
share|improve this answer
    
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 at 10:38

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
share|improve this answer
    
Thank you. Though this solution is completely infeasible, +1 for demonstrating that at least I'm not missing something simple! –  dotancohen Jul 3 at 10:19
    
Cool. How is it unsuitable? –  Paul Jul 3 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 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 at 10:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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