Post reply

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

  • 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))

  • February 6, 2015 at 2:36 pm

    #1776178

    COUNT(DISTINCT person_id)

  • February 9, 2015 at 1:40 pm

    #1776486

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

  • 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/

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply