|
View previous topic :: View next topic
|
| Author |
Message |
Shruti Takkar
New User
Joined: 09 Apr 2015
Posts: 5
Location: india
|
|
|
|
Hi ,
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
from t1
============================================
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.
Regards,
Shruti |
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| 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? |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
is this something you are expecting?
| Code: |
select
Count(case when (t1.a = 1 and t1.b<> 2) then 1 else 0 end) as A_COUNT
, Count(case when (t1.b = 2 and t1.a<>1) then 1 else 0 end) as B_COUNT
from t1
|
Thanks,
Chandan |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Code: |
Select innr.type,sum(innr.cnt )
from
(select
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 |
|
|
| Back to top |
|
|
|
|
