Post reply

How to Group Rows and Count Rows in the Group

  • January 29, 2007 at 10:45 am

    #171193

    I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for a particular date based on a 'datetime' field that holds the date and time of each record's creation.

    this is the best I can come up with:

    SELECT

    DISTINCT(LEFT(post_date,11)) AS post_date,COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts

    FROM log_directory_contacts

    GROUP BY post_date

    The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.

    What am I doing wrong? Thanks!

  • January 29, 2007 at 11:39 am

    #686222

    Your 'post_date' field will include a time component too. So really you want to group by dateadd(day,0,datediff(day,0,post_date)) (which is a simple way of truncating the time - counts the number of days between a point in time (0) and then adds it back again).

    So your query becomes:

    SELECT dateadd(day,0,datediff(day,0,post_date)) as post_date, count(*) as total_posts

    FROM log_directory_contacts

    GROUP BY dateadd(day,0,datediff(day,0,post_date))

    Rob Farley
    LobsterPot Solutions & Adelaide SQL Server User Group
    Company: http://www.lobsterpot.com.au
    Blog: http://blogs.lobsterpot.com.au

  • January 30, 2007 at 7:56 am

    #686454

    Thank you for the help - that worked beautifully. It is odd to me that a language as robust as T-SQL doesn't have a less 'round-about' way to doing that. It seems like a pretty common task. But that's just my inchoate opinion!

  • January 30, 2007 at 9:50 pm

    #686711

    Heh... Even in the "higher priced spread" of Oracle, you have to TRUNC(somedate) to get the date with no time (midnight, really).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply