Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: SQL difficulty

  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: SQL difficulty

    Hi
    From the below table (sporting event success):
    Code:
    Occassion         Team                 Location            Date
    Ashes 2009        England              Lord's              2009-07-16
    Ashes 2009        Australia            Headingley          2009-08-07
    Ashes 2009        England              Oval                2009-08-20
    I would like to get the following resultset:
    Code:
    Team            Score        Result
    England         2            Winners
    Australia       1            Losers
    I'm not sure how to do this, and have tried all sorts of variations using SUM() and COUNT(). I considered using a lookup table:
    Code:
    Team            Status
    England         Winners
    Australia       Losers
    but one day Australia might actually win something (hard to believe, I know), invalidating the design.
    Any help would be highly appreciated - I think I could then use the same logic in my Olympics 2008 database too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry can't help

    perhaps StarTrekker, or one of his cobbers such as JimWright may be able to....
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    perhaps StarTrekker, or one of his cobbers such as JimWright may be able to....
    Maybe - although their compatriots have not had much success recently so I am not holding out too much hope

    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    pootle flump, If you can assume, you will always have a best of 3 match, this might work (note: I am not an expert at Access SQL so this will be a close approximation, if it works):
    Code:
    SELECT TEAM, SCORE, IIF(SCORE=2,'WINNERS','LOSERS') AS RESULTS
    FROM(SELECT TEAM, COUNT(*) AS SCORE
         FROM table-name
         GROUP BY TEAM
        ) AS A
    This also assumes you just have the 3 (or 2) rows for the games. If you have multiple game (a set every year?), you might be able to add the Date to get the correct counts.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks Stealth

    Unfortunatly, we can't assume that. As it happens it was a five match series. IIRC Australia were unable to bowl out the weakest England tail in a decade so drew the first match. As such, it could potentially have ended 5-0 at one extreme or 0-5 at the other (as predicted by Glenn McGrath), and anything in between.

    Also, I would like this to be extensible. For example, if I entered Olympic Golds 2008 then Great Britain would total 19 and Australia only 14. Not to mention the rugby....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    This is a little more complicated but should get the output you want (as long as it is syntactically correct for Access):
    Code:
    SELECT RESULT.OCCASION
         , RESULT.TEAM
         , RESULT.SCORE
         , IIF(RESULT.SCORE=WIN_AMOUNT.WIN_NUM,'WINNERS','LOSERS') AS RESULTS
    FROM(SELECT OCCASION, TEAM, COUNT(*) AS SCORE
         FROM table-name
         GROUP BY OCCASION, TEAM
        ) AS RESULT
       INNER JOIN
       (SELECT OCCASION, MAX(SCORE) AS WIN_NUM
        FROM (SELECT OCCASION, TEAM, COUNT(*) AS SCORE
               FROM table-name
               GROUP BY OCCASION, TEAM
              ) AS B
        GROUP BY OCCASION <== (missing from original post and added for correctness)
        ) AS WIN_AMOUNT
       ON RESULT.OCCASION = WIN_AMOUNT.OCCASION
    The nested table B gets the distinct Occasion, Team and the number of Wins
    The outer query WIN_AMOUNT, gets the maximum number of wins per event from B.
    The nested table RESULT gets the distinct Occasion, Team and number of Wins.
    RESULT is joined to WIN_AMOUNT by Occasion to return for each Occasion, the Teams that Played, How many wins they had (SCORE) and compares the Result Score to the Win_Amount Score. If they match then they are the Winners. If they don't, they are the Losers.
    Last edited by Stealth_DBA; 08-27-09 at 08:55.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Stealth - thnakyou very much indeed. That is quite complicated! I think you are missing a GROUP BY on the final derived table, but that is some air code!

    Really appreciate the time you spent on this
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    That looks an interesting solution there, but I feel that to model the real world you'd also need to add a table to contain explanations as to why the the Aussies didn't win... calling the table "whines" could work
    I was thinking of the self justifications
    eg
    thats not real rugby
    theres too many non British players
    the poms are cheats
    time wasting shouldn't be allowed (unless its an Aussie doing it)
    we would have won but someone poisoned our players
    ....y'get the picture
    ...although on second thoughts table WhinesFromtheStrines may be a multi million row table.....
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Oops, You are correct about the missing GROUP BY. I edited the original post and noted that this was done in case anyone else just looks at the code and misses your correction.

  10. #10
    Join Date
    May 2009
    Posts
    258
    Just to add one more query to the mix:
    Code:
    SELECT B.Occassion, B.Team, B.Score, IIf(Score/Total>.5,'Winner','Loser') AS Result
    FROM (SELECT Occassion, COUNT(*) AS Total
    FROM table-name
    GROUP BY Occassion) A
    INNER JOIN
    (SELECT Occassion, Team, COUNT(*) AS Score
    FROM table-name
    GROUP BY Occassion, Team) B
    ON A.Occassion = B.Occassion
    This query finds the total for an occassion and also the score for each occassion for each team. It computes the winner by determining which team won the majority of the time. Also, this logic only works as a two-team comparison, but you could add logic to figure the win % necessary for more than two teams.

    This query is a bit more pessimistic than Stealth's, since if there's a tie, both teams are losers, rather than winners.

    Ax

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hey that's a really good solution! I'm sure we could skew it for England to be winners in the event of a draw
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Its not your day is it Poots........
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I had my day on Sunday
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Is that when you went fishing and successfully caught something, apart from a cold?
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    * Refuses to help since the "one day Australia might win something" quip *

    ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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