CODEWITHSUNDEEP
sqlsql-server
ATMA
ATMA

Reputation: 1468

SQL: Counting the number of rows while accounting for duplicates

I have a fairly easy question that I haven't been able to solve. I have two tables, an account table and a customer table. Each row in the account table is unique but there are multiple customers that can be associated with an account id. For example, account id 101 is associated with id 1 and 2 in the customer table.

What I want to do is join the two tables so that I can run a count on the number of rows in the account table for a given date period. However, I also want to select only those accounts in the customer table where the cust value is A. So I need to join the two tables and I'm struggling with combing the join and distinct aspects. 

Acct table = 
account    ...
101          ...
102          ...
103          ...
104          ...
105          ...

Customer table = 
id          prim_acct     cust
1               101           A
1               101           A
2               102           A    
3               103           A    
3               103           A
3               103           A
4               104           R
5               105           R

Some of the incoherent things that I've tried. Because the data is so large, I'm just trying to work with the top 10 and get a count where I can isolate each account to 1.

;with CTE as
( 
  SELECT TOP 10 * FROM dbo.CL_CUSTOMER AS cus
  WHERE cus.CUSTOMER_STATUS = 'A'
) 
SELECT ac.ACCOUNT_NUMBER, COUNT(*) AS ac
GROUP BY ac.ACCOUNT_NUMBER FROM CTE;


SELECT TOP 10 cus.PRIMARY_ACCOUNT_NUMBER, COUNT(*) FROM dbo.CL_ACCOUNT AS ac 
INNER JOIN (SELECT DISTINCT ACCOUNT_NUMBER FROM dbo.CL_CUSTOMER WHERE CUSTOMER_STATUS = 'A') 
                dbo.CL_CUSTOMER AS cus ON ac.ACCOUNT_NUMBER = cus.PRIMARY_ACCOUNT_NUMBER
GROUP BY cus.PRIMARY_ACCOUNT_NUMBER
HAVING COUNT(*) >= 2;

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think you are trying to do this:

select count(distinct a.account)
from account a join
     customer c
     on a.account = c.prim_acct
where c.cust_value = 'A' and
      a.thedate between @date1 and @date2;

You can also phrase this as an exists query. That way, you don't need count(distinct):

select count(a.account)
from account a 
where exists (select 1
              from customer c
              where a.account = c.prim_acct and c.cust_value = 'A'
             ) and
      a.thedate between @date1 and @date2;

Upvotes: 1

Related Questions