6

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

 2 years ago
source link: https://www.codesd.com/item/sql-server-query-to-get-the-number-of-distinct-column-in-the-table.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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

advertisements

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 :)

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:


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK