sql query confusing me

Posted on 2011-04-29
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

    select username, count(*) from yourtable group by username
    LVL 23

    Assisted Solution

    by:Rajkumar Gs
    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

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    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…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now