Results 1 to 3 of 3

Thread: Count Query Help

  1. #1
    Join Date
    Feb 2012

    Question Count Query Help

    Hello I'm fairly new to SQL so i have a mySQL database set up and ive figured out how to most of the things that im trying to do, but im really struggling with this one problem. I am creating a league website using PHP and mySQL, ive got most of it pretty much done, but i want to be able to show some basic statistics about each league. I want to be able to show how many games each player in a particular event has played in the past 7 days, so basically I have 2 tables:

    Entrants table

    eventID playerName
    1 ------RMK147
    1 ------test123
    1 ------anothertest
    2 ------testingagain
    2 ------finaltest

    Results table

    resultID eventID playerA --- playerAScore playerB playerBScore ------date
    ---1--- -- 1 -- RMK147 --------2 ------ test123 ---0--------2012-02-27 00:00:12
    ---2--- -- 1 -- anothertest --- 2 ------ RMK147----1--------2012-02-27 00:00:12

    so im aiming for an output like this for event 1:

    playerName ---weeklyGames
    RMK147----------- 2
    anothertest --------1

    and like this for event 2:

    testingagain --------0
    finaltest ------------0

    i was trying to use a count to count the occurences of each name in playerA and playerB and join along with results.date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) to determine results in the last week, but couldnt get near the output im after. I'd be greatful if anyone could help me out, thanks
    Last edited by RMK147; 03-24-2012 at 05:03 PM. Reason: spacing

  2. #2
    Join Date
    Feb 2012
    Join is best used to collect relational data. Start with the events from a DATE period, using eventID.

  3. #3
    Join Date
    Feb 2012
    i got it working but using a sub query, if any1 could convert it to using joins that would be even better

    SELECT playerName, (SELECT COUNT(*) FROM results WHERE (playerName = playerA OR playerName = playerB) AND date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)) AS weeklyGames FROM entrants WHERE eventID = '1'

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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