CODEWITHSUNDEEP
sqlsql-serverdistinct
user1444329
user1444329

Reputation: 37

Distinct count of the number of account numbers, that appear in multiple profiles

I have a table that has account numbers in (account_num) and user profiles (profile_id). There can be multiple instances of the same account number for different profiles, or multiple instances of a profile with different account numbers. (There can't be multiple instances of a profile with the same account number).

I am trying to write a query that will give me a distinct count of the number of account numbers, that appear in multiple profiles.

I was trying the below query (although recommendation of a more efficient query would be appreciated);

Select Count(*) from
(select account_num, count(profile_id) as num_users
from dbo.sample
where account_num <> ''
group by account_num
)
where num_users >1

But I keep getting the following error;

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'where'.

I am using Microsoft SQL Server Management Studio. As an aside, would this query be different on an Oracle Server?

Any help would be much appreciated.

Upvotes: 3

Views: 963

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

The result you want can also be shown by this query:

SELECT COUNT(DISTINCT account_num) AS cnt
FROM dbo.sample    a                        --- no AS here to work in Oracle
WHERE account_num <> ''
  AND profile_id IS NOT NULL
  AND EXISTS
      ( SELECT *
        FROM dbo.sample    b 
        WHERE b.account_num = a.account_num
          AND profile_id IS NOT NULL
          AND b.PK <> a.PK                  --- the PRIMARY KEY of the table
      ) ;

Upvotes: 0

juergen d
juergen d

Reputation: 204854

try aliasing the subquery

 select Count(*) from 
    (   
        select account_num, count(profile_id) as num_users 
        from dbo.sample 
        where account_num <> '' group by account_num
    ) t 
 where num_users > 1

Upvotes: 2

Related Questions