CODEWITHSUNDEEP
sqlsql-server-2008group-bycaseconditional-statements
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6940

Checking condition while GROUP BY in SQL

How can I efficiently check if at least one record exists in group. In case two condition are met then I would like to have it in results.

I do it now by in the first step 

select 
worker,
,help_column_A=sum(case when condition='A' then 1 else 0 end)
,help_column_B=sum(case when condition='B' then 1 else 0 end)
from table
group by
worker

then in second step I do:

select 
case when help_column_A>0 and when help_column_B>0 then 'A and B'
case when help_column_A>0 then 'A'
case when help_column_B>0 then 'B'
end

The data is a large set and definitely I do not need to count the A or B to get to final desired results.

Here is example data:

table

+-----------+------------+
| worker    | condition  |
+-----------+------------+
| 1         | A          |
+-----------+------------+
| 1         | A          |
+-----------+------------+
| 1         | B          |
+-----------+------------+
| 1         | B          |
+-----------+------------+
| 2         | A          |
+-----------+------------+
| 2         | A          |
+-----------+------------+
| 2         | A          |
+-----------+------------+
| 3         | B          |
+-----------+------------+
| 3         | B          |
+-----------+------------+
| 3         | B          |
+-----------+------------+

desired results

+-----------+------------+
| worker    | text field |
+-----------+------------+
| 1         | A and B    |
+-----------+------------+
| 2         | A          |
+-----------+------------+
| 3         | B          |
+-----------+------------+

The condition in the table may evaluate to multiple cases like A, B, and C. In the example I used only A and B.

Upvotes: 1

Views: 840

Answers (3)

Andrew Bickerton
Andrew Bickerton

Reputation: 478

When you want to apply a WHERE clause to aggregate data you want to use Having:

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

ie:

SELECT
    worker,
    ,help_column_A=sum(case when condition='A' then 1 else 0 end)
    ,help_column_B=sum(case when condition='B' then 1 else 0 end)
FROM table
GROUP BY
    worker
HAVING
    sum(case when condition='A' then 1 else 0 end) > 0
    OR sum(case when condition='B' then 1 else 0 end) > 0

Upvotes: 0

Serg
Serg

Reputation: 22811

Looks like you want to aggregate distinct conditions into a string. I assume your condition doesn't contain forbidden XML characters as & or <. (It would be just a bit slower otherwise).

declare @t table (
   worker int, 
   condition varchar(10)
);
insert @t values
 (1,'A')
,(1,'A')
,(1,'A')
,(1,'A')
,(2,'B')
,(2,'B')
,(2,'B')
,(3,'C')
,(3,'A')
,(3,'B');

select worker,
    conditions=stuff((select distinct ' and ' + t2.condition 
        from @t t2 
        where t2.worker = t1.worker
        order by ' and ' + t2.condition
        for xml path('')),1,5,'') 

from @t t1
group by worker ;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Is this what you want?

select worker,
       (case when min(condition) = max(condition) then max(condition)
             else 'A and B'
        end) from table
where condition in ('A', 'B')
group by worker;

The where condition may not be necessary, but you seem focused only on As and Bs.

More generally, you could do:

select worker,
       (case when min(condition) = max(condition) then max(condition)
             else min(condition) + ' - ' + max(condition)
        end) from table
group by worker;

Upvotes: 2

Related Questions