List:General Discussion« Previous MessageNext Message »
From:John Mistler Date:June 28 2004 8:31am
Subject:SQL challenge
View as plain text  
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)

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.


- John

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