From: "John Mistler" <johnmistler@stripped>
> I have a difficult query for you that has me stumped. The table has two
> columns: rowID (MEDIUMINT-but *NOT* AUTO_INCREMENT) and theDate
(DATETIME).
> I need it to find:
>
> 1. The COUNT of REPEATED instances of the rowID in the last month.
This is relatively easy:
SELECT rowID, COUNT( rowID ) -1 AS countrepeat
FROM <tablename>
WHERE theDate < CONCAT(DATE_FORMAT(CURDATE(), '%Y-%m-'), '01') AND
theDate >= CONCAT(DATE_FORMAT(CURDATE(), '%Y-%m-'), '01') - INTERVAL 1 MONTH
GROUP BY rowID
HAVING countrepeat >0
See if I have time for #2 later...
Regards, Jigal
