Group By Clause

Helen Pippard · #1 (**permalink**) 01-13-12, 10:58

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

anacedent · #2 (**permalink**) 01-13-12, 11:08

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

/

Helen Pippard · #3 (**permalink**) 01-13-12, 11:16

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

anacedent · #4 (**permalink**) 01-13-12, 11:21

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.