sql server - Is there a better way to write this sub query as it seems to big? -


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