Counting and sorting by ID with one query [solved]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Counting and sorting by ID with one query [solved]

Post 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...
Last edited by papa on Wed Apr 01, 2009 8:01 am, edited 1 time in total.
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Counting and sorting by ID with one query

Post 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";
 
User avatar
becky-atlanta
Forum Commoner
Posts: 74
Joined: Thu Feb 26, 2009 6:26 pm
Location: Atlanta, GA

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

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