SQL SERVER QUERY to get the Number of Distinct column in table

Question

This is the sample expected result table

As you can see I have a list of tenant with corresponding Area. I need to create a column 'Count' where it will count the number of distinct Area in the given TENANT. For example tenant 'CMBINA13' has two distinct Area. So it should output 2 in the count column, same with the next tenant example having 1 distinct area only.

Here's what I have initially got

select tenantcode ,  b.name , AreaSqm  ,
       COUNT(*) OVER (PARTITION BY AreaSqm) AS 'Count'
from    TENANT

and it gives a logically incorrect output like this

Any help will be greatly appreciated :)

Answer 1

You have to count DISTINCT areasqm, but you can't do this with COUNT .. OVER, and you can't do it neither with GROUP BY tenantcode , name , AreaSqm directly. So, one solution is to count DISTINCT areasqm in a subquery, something like this:

SELECT
  t.tenantcode , name , AreaSqm  , c.areaCount
FROM TENANT AS t
INNER JOIN
(
   SELECT tenantcode, COUNT(DISTINCT areasqm) AS areaCount
   FROM tenant 
   GROUP BY tenantcode
) AS c ON t.tenantcode = c.tenantcode;

This will give you: