How to get count of a column value returned using subquery?

Question

I want to get all hrc_acct_num which are not present in acct_key column of STAGING_CUST_ACCT table. The last outer select column is throwing an error. How can I get count of a column returned using a subquery?

SELECT source_sys_cd, 
       Count(CASE 
               WHEN is_delete = 0 THEN 1 
             END) [DEL IS 0], 
       Sum(CASE 
             WHEN trans_amt = 0 THEN 1 
             ELSE 0 
           END)   [STG $0 TXN CNT], 
       Count(CASE 
               WHEN hrc_acct_num NOT IN(SELECT DISTINCT acct_key 
                                        FROM   staging_cust_acct) THEN 
               hrc_acct_num 
             END) 
FROM   staging_transactions (nolock) 
GROUP  BY source_sys_cd 
ORDER  BY source_sys_cd

score 1 · Accepted Answer · edited Jul 09 '14 at 20:36

1

You can do a LEFT JOIN to the sub query and then do a SUM when the value is null. acct_key

SELECT source_sys_cd, 
       Count(CASE 
               WHEN is_delete = 0 THEN 1 
             END) [DEL IS 0], 
       Sum(CASE 
             WHEN trans_amt = 0 THEN 1 
             ELSE 0 
           END)   [STG $0 TXN CNT], 
       SUM(CASE WHEN T.acct_key is NULL THEN 1 else 0  END ) CountNotIN

FROM   staging_transactions (nolock) s
       LEFT JOIN (SELECT DISTINCT acct_key 
                                        FROM   staging_cust_acct) t 
        s.hrc_acct_num  = t.acct_key 
GROUP  BY source_sys_cd 
ORDER  BY source_sys_cd

Here's a simplified demo

edited Jul 09 '14 at 20:36

Gordon Linoff

1,242,037
58
646
786

answered Jul 09 '14 at 20:10

Conrad Frix

51,984
12
96
155

1

Sum will work in this case, too, however, count() doesn't increment on null values, which would be returned from the case statement if the condition was not true, in this case. – Jaaz Cole Jul 09 '14 at 20:15
@JaazCole you're right. For some reason I had read with an `else 0` in there. Personally [I still prefer SUM(CASE over COUNT(CASE](http://stackoverflow.com/questions/6350154/selecting-count-from-different-criteria-on-a-table/6350268#comment7431588_6350268) – Conrad Frix Jul 09 '14 at 20:42

score 1 · Answer 2 · answered Jul 09 '14 at 20:31

You can short circuit the subquery with NOT EXISTS. It's more efficient than LEFT JOIN (SELECT DISTINCT, since you don't care about enumerating all the times it does exist.

   SELECT source_sys_cd, 
           Count(CASE is_delete WHEN 
                   WHEN is_delete = 0 THEN 1 
                 END) [DEL IS 0], 
           Count(CASE 
                   WHEN trans_amt = 0 THEN 1 
                 END) [STG $0 TXN CNT], 
           Count(CASE 
                   WHEN NOT EXISTS (SELECT 1
                                    FROM staging_cust_acct
                                    WHERE acct_key = hrc_acct_num) THEN 1
                 END) 
    FROM   staging_transactions (nolock)
    GROUP  BY source_sys_cd 
    ORDER  BY source_sys_cd

How to get count of a column value returned using subquery?

2 Answers2