Iterative Select Statement

[SimonJ621]

Hi SP,

I'm wanting to build a result set using only MySQL (and not PHP to execute the logic). I feel like I'm missing something very simple here, but let me explain what it is I'm trying to accomplish.

I want to execute the following statement, but replace the 'where' statement with a new distinct value until there are no more distinct values. The table columns are:

Indication | ClassCode | ClassDescription

The statement I would like to execute is:

select Indication, ClassCode, ClassDescription, count(ClassCode) as `count` from indications_by_class_code where Indication = 'distinct Indication value' group by ClassCode order by count desc

I want to execute this statement for each of the distinct Indication values so I have a compiled list that looks like:

Indication          Class Code           Class Desc.          Count
cancer                   X1B                    blahblahblah         67643
cancer                   Z7G                    blahblahblah         2323
cancer                   Z8P                    blahblahblah         499
solid tumor             J3J                     blahblahblah         3899
solid tumor             K2L                    blahblahblah         499
Parkinson disease    H2R                    blahblahblah         8378
Parkinson disease    Y3P                    blahblahblah          645

In PHP, I would do something like:

$query1 = 'select distinct Indication from drug_profiles';
$result = mysql_query($query1);
while ($row = mysql_fetch_assoc($result)) {
  $indication = $row['Indication'];
  $query2 = "select Indication, ClassCode, ClassDescription, count(ClassCode) as `count` from indications_by_class_code where Indication = '{$indication}' group by ClassCode order by count desc";
  $result2 = mysql_query($query2);
  while ($resultRow = mysql_fetch_array($result2)) {
    // print out result
  }
} 

Like I said, I feel like I'm not seeing something that is very obvious here. Would appreciate any help.

Thanks,
Jason

[r937]

SELECT Indication
     , ClassCode
     , ClassDescription
     , COUNT(ClassCode) as `count` 
  FROM indications_by_class_code 
GROUP 
    BY Indication
     , ClassCode 
ORDER 
    BY `count` DESC

[SimonJ621]

Thanks r,

I knew there was something simple I was missing! Two values for the group by... thank you.