sql query confusing me


I have a table with all the acceses of the users to my website. In this table I just store the username and the date.

I need to get a list with the usernames that have logged in and how many times have they done it.

I'm confused about how can I use the 'group by' and 'count' modifiers to get this info.


user1, date1
user2, date2
user1, date 3
user1, date 4
user3, date5
user3, date6
user1, date 7

I will need to obtain the data:
user 1 - 3 times
user 2 - 1 time
user3 - 2 times

Probably it's trivial, but I can't handle it...
Thanks a lot and kind regards.
Who is Participating?
select username, count(*) from yourtable group by username
Rajkumar GsSoftware EngineerCommented:
ralmada already answered.

But If you really mean to fetch results in the same manner as you shown in your expected result
user 1 - 3 times
user 2 - 1 time
user3 - 2 times

try this also.
select username + ' - ' + cast(count(logindate) as varchar(10)) + ' times' as Logins
from yourtable
group by username

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.