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