CODEWITHSUNDEEP
sqlsql-servert-sql
Siavosh Tehrani
Siavosh Tehrani

Reputation: 135

Getting distinct count in SQL Server

I have three tables:

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

Upvotes: 1

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 3

Related Questions