I have to execute below problem in DB2 query, please suggest some valuable options.
Consider Table A having account details.
I need to count accounts from Table A for multiple scenarios.
I can count accounts using multiple case when conditions like below.
select Count(case when t1.a = 1 then 1 else 0 end) as A_COUNT
, Count(case when t1.b = 2 then 1 else 0 end) as B_COUNT
My problem here is,, if my account is counted in A_COUNT, it should not get double counted in B_COUNT.
There are multiple when conditions and it is not possible to tweak them to make all conditions mutually exclusive.
you will have to explain better. tell us the input table and sample values and expected output and then it goes easy for anyone to provide you a solution. why do you not do group by on account number and those t1.a,t1.b and so on?
Select innr.type,sum(innr.cnt )
case when t1.a = 1 then 1 else 0
when t1.b = 2 then 1 else 0 end as cnt
,case when t1.a = 1 then 'A'
when t1.b = 2 then 'B' end as type
from table temp; ) as innr
group by innr.type