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

I'm not sure if I understand, but have you tried something like this:

select rowID,COUNT(*) AS cnt
  from theTable
  where month(theDate) = month(curdate())
  group by rowID
  having cnt>1;

If you by "last month" meant the last in the dataset, you could find the
month by issuing:

select @m:=month(max(theDate)) from theTable;

You say you want the answer "4" when the count is 5...? You can subtract one
from the count in the query:

select rowID,COUNT(*)-1 AS cnt
  from theTable
  where month(theDate) = @m
  group by rowID
  having cnt>0;

> 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.

You want to group by week, you can get the week using the week() function.
For weeks starting on monday, the second parameter should be 1. You want the
average of the counts... try using a temporary table, something like this:

create temporary table tmp1 select
  week(theDate,1) AS week, rowID, count(*)-1 AS cnt,
  from theTable
  where month(theDate) = @m
  group by week,rowID
  having cnt>0;
select week,avg(cnt) from tmp1 group by week;

--
Roger

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