SQL - How can I get the number of duplicates in the non-aggregated result?

user_1087924 0
Viewed 34

Suppose I have a table tb such that

select * from tb

returns

ID | City     | Country
1  | New York | US     
2  | Chicago  | US     
3  | Boston   | US     
4  | Beijing  | China  
5  | Shanghai | China  
6  | London   | UK

What is the easiest way to write a query that can return the following result?

ID | City     | Country | Count
1  | New York | US      | 3
2  | Chicago  | US      | 3
3  | Boston   | US      | 3
4  | Beijing  | China   | 2
5  | Shanghai | China   | 2
6  | London   | UK      | 1

The only solution I can think of is 

with cte as (select country, count(1) as Count from tb group by country)
select tb.*, cte.Count from tb join cte on tb.Country = cte.Country

But I feel that is not succinct enough. I am wondering if there is anything like Duplicate_Number() over (partition by country) to do this.

count tsql sql-server
2 Answers

Try this:

select * 
      ,COUNT(*) OVER (PARTITION BY Country)
from tb

The OVER clause

Determines the partitioning and ordering of a rowset before the associated window function is applied.

So, we are basically telling to COUNT the records, but to group the rows per COUNTRY.

user_10803540

Another approach to achieve the result :

select t1.*, t2.Country_Count from tb t1
join 
    (select country, count(country) Country_Count from tb group by country) t2
on t1.country=t2.country
order by t1.id

SQL HERE

user_56959530
Related