sql - SQL Server 中的计数总和

Question

我有这样的数据，

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 

我写了上面的查询来计算住在同一州的人

counts country      state
2     india         hyderabad 
2      US           ohio      

你能帮我写一个查询，它会返回计数的总和，然后在总和中加 1，在此先感谢

标签： sqlsql-server

Answer 1

您可以将初始查询设置为CTE（公用表表达式）以求和它们的计数：

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