Re: [PHP-DB] group by day

2005-06-30 Thread Ross Honniball 
I have never used the DISTINCT clause, so I won't attempt to help in any 
detail, but look it up in the manual. I think it will help you.


Regards ... Ross

Larry Sandwick wrote:


Can you help me with this query below  Mgr's could circumvent the process by
login 20 times in 1 day and change the login attempts. This is a rolling 30
window my upper mgmt would like to track. 




select user, count(user) as num from LoginTrack 
where user and t='M' and DATE_SUB(CURDATE(),INTERVAL 30 DAY) <=date 
group by user 
order by num desc";



I have a dilemma trying to combine the data below. The problem is that when
users login several times a day I only want to count it a 1 login not 5 or
more. I tried group by date and because the time is in the date that did not
work. Any help would be appreciated !!!

Ideally the data below should return 

mgrtft  1
mgrschultz   2
mgrreid1


// MySql Table data below:
varchardatetimechar   
user date  t

mgrtft  2005-06-21 10:17:00 M
mgrtft  2005-06-21 10:16:00 M
mgrschultz  2005-06-21 09:12:00 M
mgrschultz  2005-06-21 08:56:00 M
mgrschultz  2005-06-21 08:26:00 M
mgrreid 2005-06-21 08:26:00 M
mgrschultz  2005-05-21 08:16:00 M
mgrschultz  2005-06-21 08:07:00 M
mgrtft  2005-06-21 07:46:00 M


Larry Sandwick
Sarreid, Ltd.
www.sarreid.com
Network/System Administrator
P:(252) 291-1414 223
F:(252) 237-1592



 



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] group by day (unix timestamp provided)

2002-10-24 Thread joakim . andersson 
> How can I get mySQL to group stuff by the day? my date 
> coloumn is a UNIX
> timestamp.

SELECT whatever FROM my_table GROUP BY FROM_UNIXTIME(timestamp_col,
'%Y-%m-%d')

Regards
Joakim

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php