计算此查询的记录的个数-对之前的不同对进行计数

Question

这是我的T-SQL查询代码:

SELECT DISTINCT 
StateProvince, City
FROM
adventureworks.SalesLT.Address
WHERE 
CountryRegion LIKE 'Canada' 
AND StateProvince != 'Quebec'
ORDER BY 
StateProvince, City

我试着用这个，但是没有成功

SELECT  
COUNT (DISTINCT StateProvince, City) OVER (PARTITION BY )
FROM
adventureworks.SalesLT.Address
WHERE 
CountryRegion LIKE 'Canada' 
AND StateProvince != 'Quebec'
ORDER BY 
StateProvince, City

Answer 1

对于COUNT, SUM等聚合函数，在T-SQL中最好使用GROUP BY。

SELECT COUNT(*)
From adventureworks.SalesLT.Address
WHERE CountryRegion LIKE 'Canada' AND StateProvince != 'Quebec'
GROUP BY City, StateProvince
ORDER BY StateProvince, City