Sum of Count in SQL Server

Question

I have a data like this,

id  state   country name    years
1   ohio        US          steve       2020-08-11
2   delhi       india       priya       2019-09-01
3   ohio        US          alex        2020-07-11
4   NY          US          kristen     2018-06-11
5   hyderabad   india       riya        2019-08-16
6   hyderabad   india       isha        2019-05-04

select (count(*)) as 'Counts',country, state
from dbo.people
group by country, state
having count(*)>1 

I have written the above query to count people living in same state

counts country      state
2     india         hyderabad 
2      US           ohio      

could you please help me to write a query that will return the sum of count and then add 1 to the sum, Thanks in advance

Answer 1

You can set your initial query as a CTE (Common Table Expression) to sum their counts :

with initial_query as (
  select (count(*)) as 'Counts',country, state
  from dbo.people
  group by country, state
  having count(*)>1 
)
select sum(Counts) + 1 as Counts
from initial_query