View Full Version : Count / Group By

Taylor_1978

09-14-2006, 07:38 PM

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! :thumbsup:

Taylor.

Taylor_1978

09-14-2006, 10:15 PM

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:

// 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:

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! :thumbsup:

Taylor.

marek_mar

09-15-2006, 12:59 AM

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.

Taylor_1978

09-15-2006, 01:19 AM

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...

Taylor_1978

09-15-2006, 11:37 AM

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.

guelphdad

09-15-2006, 01:58 PM

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.

Taylor_1978

09-16-2006, 01:32 AM

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

guelphdad

09-16-2006, 02:22 AM

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.

Taylor_1978

09-16-2006, 12:14 PM

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?

Fumigator

09-16-2006, 06:27 PM

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.

guelphdad

09-16-2006, 08:14 PM

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.

Taylor_1978

09-17-2006, 01:22 AM

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?

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.

Taylor_1978

09-17-2006, 03:39 PM

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.

Taylor_1978

09-17-2006, 04:41 PM

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...

:thumbsup:

Powered by vBulletin® Version 4.2.2 Copyright © 2016 vBulletin Solutions, Inc. All rights reserved.