Calculate Head Cout

• hoseam

  SSCertifiable

  Points: 5533

  More actions

  March 7, 2016 at 1:34 am

  #324674

  USE AdventureWorks2012;

  GO

  SELECT p.FirstName, p.LastName

  ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number"

  ,RANK() OVER (ORDER BY a.PostalCode) AS Rank

  ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank"

  ,NTILE(4) OVER (ORDER BY a.PostalCode) AS Quartile

  ,s.SalesYTD

  ,a.PostalCode

  FROM Sales.SalesPerson AS s

  INNER JOIN Person.Person AS p

  ON s.BusinessEntityID = p.BusinessEntityID

  INNER JOIN Person.Address AS a

  ON a.AddressID = p.BusinessEntityID

  WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;

  In the query above, I want to know how do I calculate the head count, as in How many people stay in the same Postal Code area?

• xanthos

  SSCrazy

  Points: 2510

  More actions

  March 7, 2016 at 1:41 am

  #1863398

  Try the following

  COUNT(1) OVER (Partition BY a.PostalCode)