February 6, 2015 at 2:33 pm
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
COUNT(DISTINCT person_id)
February 9, 2015 at 1:40 pm
thank you very much. how simple. I couldn't see the forest for the trees!
February 10, 2015 at 12:13 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy