Query

marius@msil.no · #1 (**permalink**) 02-12-09, 12:53

Hi

Im new at this and need some help.

Im using MyISAM database.

Problem:
I have a table with 3 Fields. Id, date and value(value = 1 or 0)

I want a query that gives me the number of times the value is 1 in succession (after another) (after another is based on the date)

result:

From 2008-02-11 untill 2009-02-15, 5 times

Is there anyone that can help me with this?

mike_bike_kite · #2 (**permalink**) 02-12-09, 13:05

You really need to provide some test data ie if 3 consequtive days have value 1 then does that count as 2 or 3? Anyway this might start you off (it assumes your dates are straight dates and don't have times) :

Code:

select count(*)
from   MyTable t1, MyTable t2
where  t1.date > START_DATE
       and t1.date <= END_DATE
       and t1.value = 1
       and t2.date = DATE_ADD( t1.date, interval -1 day )
       and t2.value = 1

PS I haven't tried it

marius@msil.no · #3 (**permalink**) 02-12-09, 13:16

Hi, if there is 3 consequetive days, is will count as 3. The date also includes time

marius@msil.no · #4 (**permalink**) 02-12-09, 13:30

i can give you all info: because my first question was simplyfied.

I am making a page for my footballteam. I want to take out info on the following: Number of victory after another,This means that kamp.malmsil (goals for my team) must be larger than kamp.malmot(goal against). I must also have the date when the vicroties started and when it stopped.

<?

$query = "
SELECT
kamp.kid,
kamp.rating,
lag.navn,
lag.lid,
kamp.dato,
kamp.alvor,
kamp.plass,
klubb.klid,
klubb.arenanavn,
kamp.malmsil,
kamp.malmot,
kamp.sted
FROM kamp
INNER JOIN lag ON lag.lid = kamp.lag_lid
INNER JOIN sesong ON kamp.sesid = sesong.sesid
LEFT JOIN klubb ON lag.lid = klubb.klid;";

this is the query that must be changed

r937 · #5 (**permalink**) 02-12-09, 13:58

"number of victory after another" is called winning streak

generally, stats are often kept just for streak, so a value of 3L would mean that the team's last three results were losses, while 5W would mean the last five games were wins (in each case, the game before that was the opposite, a win or a loss)

regarding your table relationships...

1) how many lag.lid for each kamp.lag_lid?
2) how many sesong.sesid for each kamp.sesid?
3) how many klubb.klid for each lag.lid?

marius@msil.no · #6 (**permalink**) 02-12-09, 14:16

We have all the matches since 1999, that means about 250. on a page og useless stats i want it to view the period from 1999 - 2009 with most wins in a row.

its build like this.

in every kamp (match) you have 1 lag (team we are playing against).

The kamp also is attached to the season its been played inn. The season and klubb field is nothing to think about, this is only there for the information.

mike_bike_kite · #7 (**permalink**) 02-12-09, 15:31

Quote:

Originally Posted by marius@msil.no

Hi, if there is 3 consequetive days, is will count as 3. The date also includes time

If you had a winning streak of 3 followed by another one of 4 would that be 7 for that time period? or are you looking for the longest winning streak ie 4?

PS1 Your field names read like Greek to me but I assume dato is the date
PS2 I just read your the part where you explain the fields
PS3 having now just finished a bottle of wine I feel incapable of understanding how many klubb.klid go with each lag.lid

mike_bike_kite · #8 (**permalink**) 02-13-09, 04:31

I think this works but I can't test it on my side. What language are we working with here? Do I get a prize?

Code:

select max(streak) + sign( max(streak) )
from  (
      select count(*) as streak
      from   kamp t1, kamp t2
      where  t1.dato > START_DATO
             and t1.dato <= END_DATO
             and t1.value = 1
             and t1.malmsil > t1.malmot
             and date( t2.dato ) = DATE_ADD( date( t1.dato ), interval -1 day )
             and t2.malmsil > t2.malmot
        ) streaks

Internet Services

Web Development

Digital Marketing

Consumer Tech