i have tried retrieve count of records depened on different conditions.i got expected output seems big me.
select count(b.verificationdtl_gid) received, ( select count(b.verificationdtl_gid) avs_trn_tverification inner join avs_trn_tverificationdtl b on a.verification_gid =b.verification_gid inner join ver_mst_tverifier c on a.verifier_gid = c.verifier_gid c.verifier_gid='vfi1601203046' , b.sap_flag<>'y' )as normal, ( select count(b.verificationdtl_gid) avs_trn_tverification inner join avs_trn_tverificationdtl b on a.verification_gid =b.verification_gid inner join ver_mst_tverifier c on a.verifier_gid = c.verifier_gid c.verifier_gid='vfi1601203046' , b.verification_status='reject' ) reject, ( select count(b.verificationdtl_gid) avs_trn_tverification inner join avs_trn_tverificationdtl b on a.verification_gid =b.verification_gid inner join ver_mst_tverifier c on a.verifier_gid = c.verifier_gid c.verifier_gid='vfi1601203046' , b.verification_status='decline' )as decline avs_trn_tverification inner join avs_trn_tverificationdtl b on a.verification_gid =b.verification_gid inner join ver_mst_tverifier c on a.verifier_gid = c.verifier_gid c.verifier_gid='vfi1601203046'
if query executes produces following result
received normal reject decline ----------- ----------- ----------- ----------- 33 24 0 2
use conditional aggregation instead:
select count(b.verificationdtl_gid) received, count(case when b.sap_flag <> 'y' b.verificationdtl_gid end) normal, count(case when b.verification_status ='reject' b.verificationdtl_gid end) reject, count(case when b.verification_status ='decline' b.verificationdtl_gid end) reject avs_trn_tverification inner join avs_trn_tverificationdtl b on a.verification_gid = b.verification_gid inner join ver_mst_tverifier c on a.verifier_gid = c.verifier_gid c.verifier_gid = 'vfi1601203046'
Comments
Post a Comment