Results 1 to 8 of 8

Thread: Query

  1. #1
    Join Date
    Feb 2009
    Posts
    4

    Unanswered: Query

    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?

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  3. #3
    Join Date
    Feb 2009
    Posts
    4
    Hi, if there is 3 consequetive days, is will count as 3. The date also includes time

  4. #4
    Join Date
    Feb 2009
    Posts
    4
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2009
    Posts
    4
    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.

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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
    Last edited by mike_bike_kite; 02-12-09 at 16:36.

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •