SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Enthusiast SimonJ621's Avatar
    Join Date
    Mar 2006
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Iterative Select Statement

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT Indication
         , ClassCode
         , ClassDescription
         , COUNT(ClassCode) as `count` 
      FROM indications_by_class_code 
    GROUP 
        BY Indication
         , ClassCode 
    ORDER 
        BY `count` DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast SimonJ621's Avatar
    Join Date
    Mar 2006
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r,

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •