MySQL

GROUP BY partial field

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Oct 2004

Posts: 29

Reputation: trashed is an unknown quantity at this point

Solved Threads: 0

trashed

Offline

Light Poster

GROUP BY partial field

Feb 1st, 2009

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

 Help with Code Tags 
 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):

 Help with Code Tags 
 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

OttoBit.org - IT News & Forums

•

Join Date: Jan 2009

Posts: 8

Reputation: supportresort is an unknown quantity at this point

Solved Threads: 2

supportresort supportresort is offline

Offline

Newbie Poster

Re: GROUP BY partial field

Feb 1st, 2009

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:

 Help with Code Tags 
 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.

Support Resort
http://www.supportresort.com
Bringing offshore expertise to the world