List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:June 28 2004 9:11am
Subject:Re: SQL challenge
View as plain text  
Hi John,


> O.K. you SQL gurus--
>
> 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.
>
> - so if there are 5 rows with the same rowID in the last month, it would
> return "4" (I can only seem to get it to return "10" WHERE t1.rowID =
> t2.rowID AND t1.theDate > t1.theDate)

Dunno if it works for you ... but:

why not simply do a COUNT(ROWID) grouped by ROWID and
TheDate month and subtract 1 from the count?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com


> 2. The AVERAGE number of REPEATED instances of the rowID per week (Monday
> 00:00:00 through Monday 00:00:00 one week later) in the last month.
>
> If I need to add table columns I certainly can.
>
> THANKS!
>
> - John
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
SQL challengeJohn Mistler28 Jun
  • Re: SQL challengeMartijn Tonies28 Jun
  • Re: SQL challengeJigal van Hemert28 Jun
  • Re: SQL challengeRoger Baklund28 Jun
    • Re: SQL challengeJohn Mistler28 Jun
      • Re: SQL challengeJigal van Hemert29 Jun
Re: SQL challengeSGreen29 Jun