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:

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

PHP Code:
$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