distinct count with case statement

[devendrap]

Hi,

I have a question. Appereciate your help. SQL needs distinct count with
different column as per case statement conditions.

Table data :
Source Col1 Col2 Col3
Plant1 x1
Plant1 x2
Plant2 x1 y1
Plant2 x1 y1
Plant2 x2 y2

SQL ????????
Select Source ,
distinct count ( Case When Source = 'Plant1' Then Col1
When Source = 'Plant2' Then (Col2 || col3) End )

Result should be :
Source Count
Plant1 2
Plant2 2

Could you please help me to put the sql code.

Thanks,

 

[RogerCruse]

Couldn't you have your original sql and then a derived table -

select a1, count(distinct a2)
(select source, (case when source='plant1' then col1 else col2||col3 end) from table1
group by 1
) fred (a1,a2)

I haven't tried this (I'm at home!), I hope it helps.

Roger...

 

[RogerCruse]

Take 2.

I've now tested, corrected and proven the SQL. Here it is in full (with table setup) -

CREATE multiset TABLE tektips
(
source1 char(10) not null
,col1 CHAR(10)
,col2 char(10)
,col3 char(10))
PRIMARY INDEX( col1 );

insert into tektips values ('plant1','x1',null,null);
insert into tektips values ('plant1','x2',null,null);
insert into tektips values ('plant2',null,'x1','y1');
insert into tektips values ('plant2',null,'x1','y1');
insert into tektips values ('plant2',null,'x2','y2');

select a1, count(distinct a2)
from
(select source1, (case when source1='plant1' then col1 else col2||col3 end) from tektips
) fred (a1,a2)
group by 1


This produces the results you wanted