Normally I would have joined another results set containing a GROUP BY, but it appears you can use the OVER window function without a PARTITION BY / ORDER BY statement.
The syntax is COUNT(*) OVER () AS [RecordCount]
I use it below to return the count of addresses in AdventureWorks.
SELECT DISTINCT
a.[City]
,sp.Name As StateProvince
,cr.Name AS CountryRegion
,COUNT(*) OVER (PARTITION BY sp.[StateProvinceID]) AS AddressesInThisProvince
,COUNT(*) OVER (PARTITION BY cr.CountryRegionCode) AS AddressesInThisCountry
,COUNT(*) OVER () AS TotalAddresses
FROM [AdventureWorks2014].[Person].[Address] a
INNER JOIN [AdventureWorks2014].[Person].[StateProvince] sp
ON a.[StateProvinceID] = sp.[StateProvinceID]
INNER JOIN [AdventureWorks2014].[Person].[CountryRegion] cr
ON sp.CountryRegionCode = cr.CountryRegionCode
WHERE cr.Name = 'Germany'
ORDER BY 1,2,3
No comments:
Post a Comment