Re: SQL challenge - Martijn Tonies - com.mysql.lists.mysql

7 messages in com.mysql.lists.mysqlRe: SQL challenge

From	Sent On	Attachments
John Mistler	28 Jun 2004 01:30
Martijn Tonies	28 Jun 2004 02:11
Jigal van Hemert	28 Jun 2004 02:30
Roger Baklund	28 Jun 2004 02:36
John Mistler	28 Jun 2004 13:17
Jigal van Hemert	29 Jun 2004 00:29
SGr...@unimin.com	29 Jun 2004 06:26

Subject:	Re: SQL challenge
From:	Martijn Tonies (m.to...@upscene.com)
Date:	06/28/2004 02:11:18 AM
List:	com.mysql.lists.mysql

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,

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!