1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

using the min() function inside of the case statement

Discussion in 'SQL PL/SQL' started by polostar, Nov 2, 2010.

  1. polostar

    polostar Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I'm having trouble with the following select statment

    select
    patient_id
    , sum(case when ch.cancer_site_id in ('3', '39', '12') and min(ch.diagnosis_age) < 40 then '1' else '0' end) as Breast
    from ...
    group by patient_id

    I'm getting a "not a single-group group function" error. I'm not sure if it's just a syntax error or if this function isn't supported in oracle. If it's not allowed, what I'm trying to find out is if the earliest age a patient was diagnosed with breast cancer is less than 40 years old. The 3 different cancer site id's are the different types of breast cancer that get grouped together. If you have a better way to do this then let me know.
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi polostar, what you provide is inadequate to figure it out.
    If you provide the description of table and requirement, we can do better justice to your requirement.

    However remember one thing , whenever you are using group by clause, specify the condition in having clause.

    Hope got better.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    And you should since ch.cancer_site_id is not in the group by clause. I believe this is a case where subquery factoring might be of use:

    Code (SQL):
     
    WITH diag_age AS(
    SELECT cancer_site_id, MIN(diagnosis_age) m_diag_age
    FROM ...
    WHERE cancer_site_id IN ('3', '39', '12')
    GROUP BY cancer_site_id
    )
    SELECT
    patient_id
    , SUM(CASE WHEN d.m_diag_age < 40 THEN '1' ELSE '0' END) AS Breast
    FROM ..., diag_age d
    ...
    GROUP BY patient_id;

     
    This computes the min(diagnosis_age) based on cancer_site_id and then allows you to query the resulting value in the final query. You will likely need to modify the query somewhat but the example posted should get you started.
     
    polostar likes this.
  4. polostar

    polostar Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Sorry about not putting enough information up. This is my first time posting to an SQL forum.

    The main table I'm working with is set up thusly.

    HTML:
     PatientID           diagnosis_age       cancer_site_id

    12345678           35                        4
    12345678           40                        4
    12345678           42                        12
    12345678           34                        31
    87654321           50                        12
     
    cancer_site_ids of 4 and 31 need to get grouped together and called breast. cancer_site_id of 12 (and others) is colon cancer.

    Eventually I need the output to look like this

    HTML:
    PatientID     min(breast_diagnosis_age) < 40 min(breast_diagnosis_age) > 40  min(colon_diagnosis_age) > 40  
    -------------------------------------------------------------------------------
    12345678                        1                      0                                          1
    87654321                        0                      0                                            1
    My final query is a lot more complicated than this, but this is the last part I can't figure out.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please see my previous post. I believe the example I have provided will help you.