Getting distinct count in SQL Server

Question

I have three tables:

companies
customers for those companies
addresses for each customer

I want to know how many companies in my database has the accounts with more that 100 addresses linked to them.

I tried this but it didn't work:

SELECT  
    COUNT(DISTINCT c.Id) 
FROM 
    Customers cu
INNER JOIN 
    Addresses ad ON ad.Customer = cu.Id
INNER JOIN 
    Companies c ON cu.company = c.Id
GROUP BY 
    cu.ID
HAVING 
    COUNT(ad.ID) > 100

Siavosh Tehrani · Accepted Answer · 2018-04-15 13:01:17Z

3

You need two levels of aggregation for this query. Here is one method:

SELECT COUNT(*)
FROM (SELECT cu.company, COUNT(DISTINCT ad.id) as num_accresses
      FROM Customers cu 
         INNER JOIN Addresses ad ON ad.Customer = cu.Id
      GROUP BY cu.company
      HAVING COUNT(DISTINCT ad.id) > 100
     ) cua;

The inner query returns the companies that have more than 100 addresses. Note the use of COUNT(DISTINCT). Presumably, two "customers" could have the same addresses.

Also, the companies table is not needed. The identifying information is in customers. You are only looking for a count, so the table is not necessary.

edited Apr 15, 2018 at 13:01

Siavosh Tehrani

1357 bronze badges

answered Apr 15, 2018 at 12:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting distinct count in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related