GROUP BY partial field

Expand Post »

Hi all.
I have a table with a this records:

MySQL Syntax (Toggle Plain Text)
  <!-- Table user_activity -->
    <user_activity>
        <id>1</id>
        <event>LOGIN</event>
        <username>user@domain.com</username>
        <domain>domain.com</domain>
        <remote_address>ip_address</remote_address>
        <date>2008-05-23 20:19:36</date>
        <comments></comments>
    </user_activity>
  <!-- Table user_activity -->
    <user_activity>
        <id>1</id>
        <event>LOGIN</event>
        <username>user@domain.com</username>
        <domain>domain.com</domain>
        <remote_address>ip_address</remote_address>
        <date>2008-05-23 20:19:36</date>
        <comments></comments>
    </user_activity>

I am trying to query the DB to get the total results of activity PER DAY. This is my query (which obviously doesn't work):

MySQL Syntax (Toggle Plain Text)
SELECT DATE, COUNT(*) FROM `user_activity` WHERE `event` LIKE "LOGIN" AND `DATE` LIKE "2008-05%" GROUP BY DATE
SELECT date, COUNT(*) FROM `user_activity` WHERE `event` LIKE "LOGIN" AND `date` LIKE "2008-05%" GROUP BY date

Since the date field has also a hour timestamp, I get thousands of results with count 1.
Is there any way to group the results only by the first 10 characters (and omit the time of day)?
Thanks

trashed

Reputation Points: 11

Solved Threads: 0

Light Poster

Offline

30 posts
since Oct 2004

Permalink

Feb 1st, 2009

Re: GROUP BY partial field

MySQL has a DATE function to extract only the date from a DATETIME field. You should be able to use a query such as the following:

sql Syntax (Toggle Plain Text)
SELECT DATE(DATE), count(*) FROM user_activity WHERE event LIKE "LOGIN" GROUP BY DATE(DATE);
select DATE(date), count(*) from user_activity where event LIKE "LOGIN" group by DATE(date);

I hope that helps.

Last edited by peter_budo; Feb 2nd, 2009 at 7:12 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

supportresort

Reputation Points: 15

Solved Threads: 2

Newbie Poster

Offline

8 posts
since Jan 2009

Permalink

Feb 2nd, 2009

Re: GROUP BY partial field

kudos!

trashed

Reputation Points: 11

Solved Threads: 0

Light Poster

Offline

30 posts
since Oct 2004

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: MY SQL reports

Next Thread in MySQL Forum Timeline: Please suggest: MsAccess to mysql

DaniWeb Message

Cancel Changes

User Name
Password