Example of Using PARTITION Clause

SELECT ContactName, Country, COUNT(*) OVER (PARTITION BY Country) FROM Customers
ORDER BY Country

This will generate the same result as this query:

SELECT ContactName, Country, 
		(SELECT COUNT(*) FROM Customers C1 WHERE C2.Country = C1.Country) 
FROM Customers C2
ORDER BY Country