how to write query to get count of unique sightings by division?

• KoldCoffee

  SSCoach

  Points: 18971

  More actions

  February 6, 2015 at 2:33 pm

  #315075

  Hi, can someone help? I need a query that will get me a count of unique sightings of a person_id per division.

  from the below sample table I need the results as follows:

  Patagonia, Central, 1

  Patagonia, URRS, 2

  Patagonia, West, 1

  Patagonia, Northeast, 2

  Patagonia, Southeast, 1

  As you can see, even though the same person_id was sighted once in URRS and three times in the West, each division gets a count of 1, for one unique sighting of that person_id in each division.

  ddl

  CREATE TABLE [dbo].[samples](

  [country_name] [nvarchar](255) NULL,

  [region_name] [nvarchar](255) NULL,

  [location_id] [float] NULL,

  [rn] [float] NULL,

  [person_id] [float] NULL,

  [activity_date] [datetime] NULL

  ) ON [PRIMARY]

  GO

  INSERT [dbo].[samples] ([country_name], [region_name], [location_id], [rn], [person_id], [activity_date]) VALUES (N'Patagonia', N'Central', 52016, 1, 2261177, CAST(0x0000A41300000000 AS DateTime))

  INSERT [dbo].[samples] ([country_name], [region_name], [location_id], [rn], [person_id], [activity_date]) VALUES (N'Patagonia', N'Central', 51896, 2, 2261177, CAST(0x0000A41300000000 AS DateTime))

  INSERT [dbo].[samples] ([country_name], [region_name], [location_id], [rn], [person_id], [activity_date]) VALUES (N'Patagonia', N'URRS', 142035, 1, 3057759, CAST(0x0000A41300000000 AS DateTime))

  INSERT [dbo].[samples] ([country_name], [region_name], [location_id], [rn], [person_id], [activity_date]) VALUES (N'Patagonia', N'West', 96635, 2, 3057759, CAST(0x0000A41300000000 AS DateTime))

  INSERT [dbo].[samples] ([country_name], [region_name], [location_id], [rn], [person_id], [activity_date]) VALUES (N'Patagonia', N'West', 53060, 3, 3057759, CAST(0x0000A41300000000 AS DateTime))

  INSERT [dbo].[samples] ([country_name], [region_name], [location_id], [rn], [person_id], [activity_date]) VALUES (N'Patagonia', N'West', 53058, 4, 3057759, CAST(0x0000A41300000000 AS DateTime))

  INSERT [dbo].[samples] ([country_name], [region_name], [location_id], [rn], [person_id], [activity_date]) VALUES (N'Patagonia', N'Northeast', 72548, 1, 3381061, CAST(0x0000A41300000000 AS DateTime))

  INSERT [dbo].[samples] ([country_name], [region_name], [location_id], [rn], [person_id], [activity_date]) VALUES (N'Patagonia', N'URRS', 51205, 1, 3995547, CAST(0x0000A41300000000 AS DateTime))

  INSERT [dbo].[samples] ([country_name], [region_name], [location_id], [rn], [person_id], [activity_date]) VALUES (N'Patagonia', N'Southeast', 47849, 1, 4092485, CAST(0x0000A41300000000 AS DateTime))

  INSERT [dbo].[samples] ([country_name], [region_name], [location_id], [rn], [person_id], [activity_date]) VALUES (N'Patagonia', N'Northeast', 66360, 1, 4138586, CAST(0x0000A41300000000 AS DateTime))

• ZZartin

  SSC-Dedicated

  Points: 30921

  More actions

  February 6, 2015 at 2:36 pm

  #1776178

  COUNT(DISTINCT person_id)

• KoldCoffee

  SSCoach

  Points: 18971

  More actions

  February 9, 2015 at 1:40 pm

  #1776486

  thank you very much. how simple. I couldn't see the forest for the trees!

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  February 10, 2015 at 12:13 am

  #1776540

  You can use below query for this:

  select country_name, region_name,COUNT(region_name)total

  FROM samples

  group by country_name, region_name

  _______________________________________________________________
  To get quick answer follow this link:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/