SQL Server 2005 - Rollup with distinct (Help)

• Ashish Kumar-1095774

  Valued Member

  Points: 68

  More actions

  March 1, 2010 at 11:29 am

  #143145

  Folks,

  I realize that group by rollup for distinct values in SQL Server 2005 is not supported. Can you please help me in writing the following situation. I have a table which keeps track of an Employee working in multiple offices located in different states. I need to get the count of Employees working in each office by state and/or office code.

  Table=WorkPlace

  State OfficeCode EmployeeId

  ==========================

  NJ O1 1

  NJ O1 2

  NJ O1 3

  NJ O2 1

  PA O3 1

  I guess I am looking for something like this:

  Select

  [State],

  OfficeCode,

  Count(Distinct EmployeeId)

  from

  WorkPlace

  Group By

  [State],

  OfficeCode

  With RollUp

  Any help will be highly appreciated.

  Ash

• brdudley

  SSCrazy

  Points: 2320

  More actions

  March 25, 2010 at 11:51 pm

  #1140136

  Here is something I put together. I added a RegionCode to your example just to see that the pattern would extend.

  Drop Table EmployeeLocation

  go

  Create Table EmployeeLocation(

  RegionCode varchar(2) not null,

  StateCode varchar(2) not null,

  OfficeCode varchar(2) not null,

  EmployeeID int not null

  )

  go

  Insert EmployeeLocation values ( 'AA', 'NJ', '01', 1 )

  Insert EmployeeLocation values ( 'AA', 'NJ', '01', 2 )

  Insert EmployeeLocation values ( 'AA', 'NJ', '01', 3 )

  Insert EmployeeLocation values ( 'AA', 'NJ', '02', 1 )

  Insert EmployeeLocation values ( 'AA', 'PA', '03', 1 )

  Insert EmployeeLocation values ( 'BB', 'IL', '04', 2 )

  Insert EmployeeLocation values ( 'BB', 'IL', '04', 2 )

  go

  --select * from EmployeeLocation

  Selectt1.RegionCode,

  t1.StateCode,

  t1.OfficeCode,

  Count(Distinct t2.EmployeeID )

  From(

  SelectRegionCode,

  StateCode,

  OfficeCode

  FromEmployeeLocation

  Group

  ByRegionCode,

  StateCode,

  OfficeCode

  WithRollup

  )t1

  JoinEmployeeLocation t2

  On1=1

  AndCoalesce( t1.RegionCode, t2.RegionCode ) = t2.RegionCode

  AndCoalesce( t1.StateCode, t2.StateCode ) = t2.StateCode

  AndCoalesce( t1.OfficeCode, t2.OfficeCode ) = t2.OfficeCode

  Group

  Byt1.RegionCode,

  t1.StateCode,

  t1.OfficeCode

  Order

  ByRegionCode,

  StateCode,

  OfficeCode

  The results look like

  RegionCodeStateCodeOfficeCode(No column name)

  NULLNULLNULL3

  AANULLNULL3

  AANJNULL3

  AANJ013

  AANJ021

  AAPANULL1

  AAPA031

  BBNULLNULL1

  BBILNULL1

  BBIL041

  The order is messed up, but could probably be put back together by adding a row number to the derived table using the rollup.

  Hope that helps.

  Brian