CODEWITHSUNDEEP
sqlsql-servercount
codeBoy
codeBoy

Reputation: 533

Issue With Distinct Count SQL Server

I am having an issue getting the count in a query of mine. I have a query that I am trying to see how many people are assigned to each car model.

I would like the data to be returned like so :

Car Model     People

Accord        5
Malibu        6
Model S       4

Right now I am getting this with the query that I have :

Car Model     People

Accord        15
Malibu        15
Model S       15

There are a total of 15 people and I am getting that in return rather than a distinct count for each model.. I will post query below:

  SELECT b.[ModelName], COUNT(DISTINCT a.[CustomerID])
  FROM firstTable a
  left join secondTable b on b.[ModelID] = a.[ModelID]    
  WHERE [Country] = 'US'
  GROUP BY b.[ModelName]

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You have a left join and are counting from the first table. You want to count from the second:

  SELECT b.[ModelName], COUNT(DISTINCT b.[ModelId])
  FROM firstTable a LEFT JOIN
       secondTable b
       on b.[ModelID] = a.[ModelID]    
  WHERE [Country] = 'US'
  GROUP BY b.[ModelName]

However, it is quite suspicious that you are aggregating by the model name in the second table, because it can be NULL. You might also just want a RIGHT JOIN in your first query (actually, I would reverse the tables and still use LEFT JOIN).

Upvotes: 3

Related Questions