CODEWITHSUNDEEP
sqlsql-server
lucgian841
lucgian841

Reputation: 2002

Get the number of same field from a table in SQL

I've the following table:

UserId                                  Start                   Stop

1550f503-914e-4f83-af2b-249d54cce369    2014-04-15 18:20:23.000 2014-04-15 18:21:38.000
1550f503-914e-4f83-af2b-249d54cce369    2014-04-15 18:20:23.000 2014-04-15 18:21:38.000
1550f503-914e-4f83-af2b-249d54cce369    2014-04-15 18:20:23.000 2014-04-15 18:21:38.000
1550f503-914e-4f83-af2b-249d54cce369    2014-04-15 18:20:23.000 2014-04-15 18:21:38.000
1550f503-914e-4f83-af2b-249d54cce369    2014-04-15 18:39:14.000 2014-04-15 18:40:02.000
1550f503-914e-4f83-af2b-249d54cce369    2014-04-15 18:41:17.000 2014-04-15 18:46:08.000
1550f503-914e-4f83-af2b-249d54cce369    2014-04-15 19:57:28.000 2014-04-15 19:59:53.000
1550f503-914e-4f83-af2b-249d54cce369    2014-04-16 08:19:48.000 2014-04-16 08:20:27.000
1550f503-914e-4f83-af2b-249d54cce369    2014-04-16 13:18:41.000 2014-04-16 14:24:23.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-16 16:10:11.000 2014-04-16 16:15:53.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-16 17:18:57.000 2014-04-16 17:20:09.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-16 20:21:27.000 2014-04-16 20:32:40.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-16 20:57:06.000 2014-04-16 20:57:41.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-16 20:57:56.000 2014-04-16 21:33:47.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-17 08:03:34.000 2014-04-17 08:19:42.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-17 08:23:04.000 2014-04-17 08:25:18.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-17 08:32:56.000 2014-04-17 08:36:13.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-17 08:40:17.000 2014-04-17 08:42:01.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-17 09:03:56.000 2014-04-17 09:04:07.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-17 09:38:37.000 2014-04-17 09:45:55.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-17 09:46:37.000 2014-04-17 09:48:35.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-17 09:48:58.000 2014-04-17 09:55:27.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-17 09:55:49.000 2014-04-17 09:57:11.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-17 10:00:32.000 2014-04-17 10:03:32.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-17 10:08:09.000 2014-04-17 10:14:04.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-17 10:16:49.000 2014-04-17 10:26:29.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-17 10:32:36.000 2014-04-17 10:37:42.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-17 10:42:01.000 2014-04-17 10:43:23.000
8e2555b7-7d7b-410b-818e-1c62f1d9268a    2014-04-17 10:58:34.000 2014-04-17 10:58:41.000
8e2555b7-7d7b-410b-818e-1c62f1d9268a    2014-04-17 10:58:53.000 2014-04-17 10:59:49.000
8e2555b7-7d7b-410b-818e-1c62f1d9268a    2014-04-17 11:02:25.000 2014-04-17 11:04:03.000
7a2e5b7c-9a3a-4ad1-ad79-946bcf2bf2f3    2014-04-17 12:43:04.000 2014-04-17 12:43:13.000

You can see that in my table some of UserId field are repeated, so I've used the following query to distinct them and obtain a table in which the UserId aren't repeated:

select DISTINCT UserId from UserSessions

Now I need to get the number of time that the first UserId is repeated in the table to get the number of time that this user access to the application, so I thought to do a query like this:

select COUNT(UserId) from UserSessions
where UserId = (select DISTINCT UserId from UserSessions)

But it doesn't work, how I can solve my problem?

Upvotes: 1

Views: 34

Answers (3)

Donal
Donal

Reputation: 32713

select UserId, COUNT(UserId) from UserSessions
GROUP BY UserId

Upvotes: 1

Yuck
Yuck

Reputation: 50855

This is a simple group by with count solution:

select UserId, count(*)
from UserSessions
group by UserId;

You can also get the min(Start) or max(Stop) to figure the first and last times they accessed the application.

You can add a having count(*) > 1 clause after the group by to see who has accessed the application more than once (or any other number).

Lots of things you can do with SQL aggregation. You should probably read a primer.

Aggregate Functions (Transact-SQL)

Upvotes: 1

Alex Szabo
Alex Szabo

Reputation: 3276

I think this should do it:

SELECT UserId, COUNT(UserId) 
FROM UserSessions
GROUP BY UserId

You don't have to include the DISTINCT USERID part in the WHERE clause, as it will group on the IDs.

Upvotes: 1

Related Questions