[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


sql query confusing me

Posted on 2011-04-29
Medium Priority
Last Modified: 2012-05-11

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.
Question by:starbase2
LVL 41

Accepted Solution

ralmada earned 1400 total points
ID: 35492312
select username, count(*) from yourtable group by username
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 600 total points
ID: 35497063
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


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question