Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14
  1. #1
    Regular Coder
    Join Date
    Jun 2003
    Location
    Australia
    Posts
    528
    Thanks
    8
    Thanked 8 Times in 8 Posts

    Count / Group By

    Okay - for the past hour or so I have been trying to decifer COUNT and GROUP BY, and how it can help me.

    I've looked over some manuals, but it just isn't quite clear - to me anyway.

    So, I figure the best way to learn will be to show what I would like to do, and if someone can give me an example of how I can do it, I might understand it a little better when it relates to me.

    I have

    TABLE: teams
    fields: id, tid, uid, lid

    TABLE: ranks
    fields: id, wins

    TABLE: matchdb
    fields: id, wid

    Basically I want to count how many times matchdb.wid is equal to teams.tid and add the result to ranks.wins -> I want this for each ranks.id; teams.tid isa record of the ranks.id

    I hope that made sense.

    Thanks in advance!

    Taylor.
    Last edited by Taylor_1978; 09-14-2006 at 06:41 PM.

  • #2
    Regular Coder
    Join Date
    Jun 2003
    Location
    Australia
    Posts
    528
    Thanks
    8
    Thanked 8 Times in 8 Posts
    Okay.. So I found some better reading material for this topic.. and I have figured out how to do what I wanted.. which means I have a new problem relating to it (although I knew this problem was coming).

    Here is what I did:

    PHP Code:
    // UPDATE Total Wins
        
    $query mysql_query("SELECT wid,COUNT(wid) AS TMwon FROM matchdb WHERE ladder='$ladder' GROUP BY wid");
        while(
    $row=mysql_fetch_array($query)){
            
    mysql_query("UPDATE ranks SET TMwon='$row[TMwon]' WHERE id='$row[wid]'") or die(mysql_error());
        } 
    Which works nice and dandy... HOWEVER.. I don't just have wid to count.. Here's an example of what I have so far.. baring in mind that I have about 10 I can think of from top of my head:

    PHP Code:
    function league_ranks_1($ladder){
    global 
    $LINFO;
        
    // UPDATE Total Wins
        
    $query mysql_query("SELECT wid,COUNT(wid) AS TMwon FROM matchdb WHERE ladder='$ladder' GROUP BY wid");
        while(
    $row=mysql_fetch_array($query)){
            
    mysql_query("UPDATE ranks SET TMwon='$row[TMwon]' WHERE id='$row[wid]'") or die(mysql_error());
        }

        
    // UPDATE Total Losses
        
    $query mysql_query("SELECT lid,COUNT(lid) AS TMlost FROM matchdb WHERE ladder='$ladder' GROUP BY lid");
        while(
    $row=mysql_fetch_array($query)){
            
    mysql_query("UPDATE ranks SET TMlost='$row[TMlost]' WHERE id='$row[lid]'") or die(mysql_error());
        } 

        
    // UPDATE Play Period Wins
        
    $query mysql_query("SELECT wid,COUNT(wid) AS PMwon FROM matchdb WHERE ladder='$ladder' AND season='$LINFO[season]' GROUP BY wid");
        while(
    $row=mysql_fetch_array($query)){
            
    mysql_query("UPDATE ranks SET PMwon='$row[PMwon]' WHERE id='$row[wid]'") or die(mysql_error());
        }

        
    // UPDATE Play Period Losses
        
    $query mysql_query("SELECT lid,COUNT(lid) AS PMlost FROM matchdb WHERE ladder='$ladder' AND season='$LINFO[season]' GROUP BY lid");
        while(
    $row=mysql_fetch_array($query)){
            
    mysql_query("UPDATE ranks SET PMlost='$row[PMlost]' WHERE id='$row[lid]'") or die(mysql_error());
        }

    Which ofcourse worries me once I get to 10 or more, or even just at 4 - as too how long this will take to execute.

    Anyone have any suggestions?

    Thanks in advance!

    Taylor.

  • #3
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Taylor_1978 View Post
    Basically I want to count how many times matchdb.wid is equal to teams.tid and add the result to ranks.wins -> I want this for each ranks.id; teams.tid isa record of the ranks.id
    Could you explain what you mean by "count how many times matchdb.wid is equal to teams.tid"?
    COUNT() is an aggregate function which means that it will return only one alue from many rows for example COUNT returns the amount of rows, MIN the minimal value etc.
    GROUP BY is needed when you select fields with an aggregate function.
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #4
    Regular Coder
    Join Date
    Jun 2003
    Location
    Australia
    Posts
    528
    Thanks
    8
    Thanked 8 Times in 8 Posts
    Sorry.. umm.. What I meant was, I want it to count how many times each number individually appears in matchdb.wid.. So if 1 appears 5 times in matchdb.wid, it then to go to ranks.TMwon and update it to 5. If 2 appears 8 times... etc

    But as I mentioned in my 2nd post, I got that working no problems.. And thats using a GROUP BY..

    Problem now is having too many of these queries...

  • #5
    Regular Coder
    Join Date
    Jun 2003
    Location
    Australia
    Posts
    528
    Thanks
    8
    Thanked 8 Times in 8 Posts
    I've now been fiddling with this for about 8 hours and just cannot seem to come up with a solution.

    I now have 10 different queries.

    Is it possible at all to put more than one COUNT in the one query..

    EG. SELECT wid, COUNT(wid) AS TMwon, COUNT(lid) AS TMlost WHERE ladder='$ladder' GROUP BY wid

    This doesn't work, I've tested it out.. it gives TMlost the same value as TMwon. But it might give you a better understanding of what I am trying to accomplish.

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    what I want to know is why you are calculating something from one table and then storing that information in a separate table. since you can calculate what you need from the first table, the second table is redundant.

  • #7
    Regular Coder
    Join Date
    Jun 2003
    Location
    Australia
    Posts
    528
    Thanks
    8
    Thanked 8 Times in 8 Posts
    There is a reason why.. Not sure how your response would in any way help what I need.. Just perhaps trust me on it lol

  • #8
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    my response helps because I'm asking you why you are storing information in a table when you can already calculate that data from an existing table.

    one of the points about good database practices is proper storage and retrieval of your data. you should not store data in a table when it can be calculated elsewhere.

    if you don't want to say why beyond "there is a reason why" then there isn't much to do to help.

  • #9
    Regular Coder
    Join Date
    Jun 2003
    Location
    Australia
    Posts
    528
    Thanks
    8
    Thanked 8 Times in 8 Posts
    Sorry, I got home from a night out and about on the town a little (okay a lot) tipsey when I wrote that last message, I didn't quite understand what you were asking... Now that I'm sober, I'm still not quite sure lol.

    The matchdb table stores the results of each game played. So it has the winner uid, loser uid, date etc. I'm then calculating how many wins each player has, and how many losses and I store that into the ranks table so it can be viewed by the players.

    Is that what you're asking? Are you suggesting that rather than storing the information I run these queries each time the standings are viewed?
    Last edited by Taylor_1978; 09-16-2006 at 11:17 AM.

  • #10
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Are you suggesting that rather than storing the information I run these queries each time the standings are viewed?
    Bingo

    If the queries are well written it will not be a drain on resources.

  • #11
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    you can also combine most of those queries above into a single query or perhaps two. you can use count on more than one column at a time.

  • #12
    Regular Coder
    Join Date
    Jun 2003
    Location
    Australia
    Posts
    528
    Thanks
    8
    Thanked 8 Times in 8 Posts
    Quote Originally Posted by Fumigator View Post
    Bingo

    If the queries are well written it will not be a drain on resources.
    Okay - I understand that I would be able to do a query each time someone views the ranking/standings but when they view the standings it would look something like this:

    Rank || Player || Total Won || Total Lost || Won Higher || Won Same || Won Lower || Points || Penalty || Total Points
    1 || Taylor || 10 || 5 || 8 || 1 || 1 || 22 || 0 || 27
    2 || Bob || 9 || 6 || 5 || 3 || 1 || 17 || 0 || 17
    3 || Betty || 2 || 4 || 2 || 0 || 0 || 2 || 0 || 2

    Each match recorded is listed as HIGHER, SAME or LOWER. This is dependant on which division the two players were in. So if a Division 2 player defeated a Division 1 player, the game would be declared HIGHER.

    Players are awarded each "play period" 3 points for defeating a player higher, 2 points for someone in the same division, and 1 point for defeating someone in a lower division. They also lose a point for losing a game.

    So I'm unsure as to how I would do a query that would tally up how many wins (higher, same & lower) and losses, tally up the points and then order them in ascending order in one query?

    Quote Originally Posted by guelphdad View Post
    you can also combine most of those queries above into a single query or perhaps two. you can use count on more than one column at a time.
    This I need help with.. I tried a different amount of combinations like:

    $query = mysql_query("SELECT wid,COUNT(wid) AS TMwon, lid, COUNT(lid) as TMlost FROM matchdb WHERE ladder='$ladder' GROUP BY wid,lid");

    But that didn't work. Was just giving TMlost the same value as TMwon.

  • #13
    Regular Coder
    Join Date
    Jun 2003
    Location
    Australia
    Posts
    528
    Thanks
    8
    Thanked 8 Times in 8 Posts
    Okay - after a few hours of tinkering with this.. This is what I came up with:

    $select = mysql_query("SELECT
    R.id,
    R.division,
    R.rank,
    R.teamname,
    R.rating,
    sum((M.wid=R.id AND M.season='$LINFO[season]')+(M.lid=R.id AND M.season='$LINFO[season]')) as PMplayed,
    sum(M.wid=R.id AND M.season='$LINFO[season]') as PMwon,
    sum(M.lid=R.id AND M.season='$LINFO[season]') as PMlost,
    sum(M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='HIGHER') as PMwonHIGHER,
    sum(M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='SAME') as PMwonSAME,
    sum(M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='LOWER') as PMwonLOWER,
    sum((PMwonHIGHER*3)+(PMwonSAME*2)+PMwonLOWER-PMlost) as points,
    R.penalty,
    sum(points-R.penalty) as TP,
    R.Ppercentage,
    R.streak,
    R.lastplayed
    FROM ranks R LEFT JOIN matchdb M ON M.ladder=R.ladder
    WHERE M.ladder='1'
    GROUP BY R.id
    ORDER BY TP DESC") or die(mysql_error());

    Everything adds up perfectly except the points where I have done (TMwonHIGHER*3)+(TMwonLOWER*2)+TMwonSAME-TMlost

    I am getting results of 348 points or 77 etc when they haven't even played a game yet.

    I also tried it as sum(PMwonHIGHER+PMwonHIGHER+PMwonHIGHER+PMwonSAME+PMwonSAME+PMwonLOWER-PMlost) as points, but that didn't work either.

    EDIT: I also tried it without the sum().. Instead doing (PMwonHIGHER*3)+(PMwonSAME*2)+PMwonLOWER-PMlost as points, but got exactly the same result.

    Any ideas? Cheers.
    Last edited by Taylor_1978; 09-17-2006 at 03:27 PM.

  • #14
    Regular Coder
    Join Date
    Jun 2003
    Location
    Australia
    Posts
    528
    Thanks
    8
    Thanked 8 Times in 8 Posts
    Well after much trial and error.. I got it:

    $select = mysql_query("SELECT
    R.id,
    R.division,
    R.rank,
    R.teamname,
    R.rating,
    sum((M.wid=R.id AND M.season='$LINFO[season]')+(M.lid=R.id AND M.season='$LINFO[season]')) as PMplayed,
    sum(M.wid=R.id AND M.season='$LINFO[season]') as PMwon,
    sum(M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='HIGHER') as PMwonHIGHER,
    sum(M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='SAME') as PMwonSAME,
    sum(M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='LOWER') as PMwonLOWER,
    sum(M.lid=R.id AND M.season='$LINFO[season]') as PMlost,
    sum((M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='HIGHER')+(M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='HIGHER')+(M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='HIGHER')+(M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='SAME')+(M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='SAME')+(M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='LOWER')-(M.lid=R.id AND M.season='$LINFO[season]')) as points,
    R.penalty,
    sum((M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='HIGHER')+(M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='HIGHER')+(M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='HIGHER')+(M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='SAME')+(M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='SAME')+(M.wid=R.id AND M.season='$LINFO[season]' AND M.diff='LOWER')-(M.lid=R.id AND M.season='$LINFO[season]'))-(R.penalty) as TP,
    R.Ppercentage,
    R.streak,
    R.lastplayed
    FROM ranks R LEFT JOIN matchdb M ON M.ladder=R.ladder
    WHERE M.ladder='1'
    GROUP BY R.id
    ORDER BY TP DESC") or die(mysql_error());

    Thanks for leading me on the right path...



  •  

    Posting Permissions

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