Results 1 to 6 of 6

Thread: Group By Clause

  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Unanswered: Group By Clause

    Hello

    Could someone kindly take a look at my SQL and help me understand why I am getting the error message: SQL not properly ended

    I understand that I need to group up my data because of the Sum in the select statement, but all is working O.K. until I put in the Sum statement and I need to group by

    Any help will be greatly appreciated


    SELECT
    substr(eoa.a,1,1) as Patient_Type,
    sum(DECODE(eoa.b,NULL,0,1)) as Total,
    DECODE(ee.c,251,420,ee.d) as Treatment_Function,
    ep.pay_id,
    pe.REG_GP as REGGP_PRACTICECODE
    FROM cfis_data.DOCTOR_SPECIALTY ds,
    data.ENC_EPI ee,
    data.ENC_OP eoa,
    Patient_Encounters pe,
    data.Encounter_Payor ep
    GROUP BY
    substr(eoa.a,1,1) as Patient_Type,
    sum(DECODE(eoa.b,NULL,0,1)) as Total,
    DECODE(ee.c,251,420,ee.d) as Treatment_Function,
    ep.pay_id,
    pe.REG_GP as REGGP_PRACTICECODE

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    try below instead
    Code:
    SELECT Substr(eoa.a, 1, 1)   AS patient_type,
           SUM(Decode(eoa.b, NULL, 0,
                             1)) AS total,
           Decode(ee.c, 251, 420,
                        ee.d)    AS treatment_function,
           ep.pay_id,
           pe.reg_gp             AS reggp_practicecode
    FROM   cfis_data.doctor_specialty ds,
           data.enc_epi ee,
           data.enc_op eoa,
           patient_encounters pe,
           data.encounter_payor ep
    GROUP  BY Substr(eoa.a, 1, 1),
              Decode(ee.c, 251, 420,
                           ee.d),
              ep.pay_id,
              pe.reg_gp
    
    /
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2011
    Posts
    82
    Thanks so much, that was quite stupid of me

    I am however now getting the following error message

    Error: ORA-00905: missing keyword

    Could I ask for a last bit of advice on this error message?

    Many Thanks
    Helen

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    on the surface it looks OK to me, but I may not be fully awake yet.
    Without your tables, I can debug further.

    COPY the SQL statement & PASTE into sqlplus.
    It will point to the offending location.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2011
    Posts
    82
    Thank you very much for your help

    I will try SQLPlus

    Thanks

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    depending upon Oracle version I have seen where Oracle does not always like keyword "AS"
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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