Page 1 of 1

Counting and sorting by ID with one query [solved]

Posted: Wed Apr 01, 2009 7:56 am
by papa
Hi,

I'm stuck with a SELECT query. Can solve it with PHP but feels like it could be solved within my sql query:

I have a table with:
fightId, fighterId

I want to select only the fighterId's that have fightId 1 for example and then group them by fighterId and count them separately.

So you get 10 votes for fighterId 10 and 5 votes for fighterId 12 for example. Instead of just counting all ids from fightId x.

Code: Select all

    
$b_query = "SELECT fighterId FROM ufc_bet
WHERE fightId = ".$row['fightId'];
 
I did try a JOIN by didn't work very well.

I am not expecting anyone to write the code for me, but don't know where to start.

With the above query I sort the fighterId's with some tacky php code and it hurts my eyes...

Re: Counting and sorting by ID with one query

Posted: Wed Apr 01, 2009 8:00 am
by papa
Think I got it actually:

Code: Select all

 
            $b_query = "SELECT COUNT(fighterId) FROM ufc_bet
                   WHERE fightId = ".$row['fightId']. " GROUP BY fighterId";
 

Re: Counting and sorting by ID with one query [solved]

Posted: Wed Apr 01, 2009 10:37 am
by becky-atlanta
Or you can also use the ORDER BY to sort in MySQL.

Code: Select all

$b_query = "SELECT COUNT(fighterId) FROM ufc_bet ORDER BY fight....