7 Replies Latest reply: Apr 15, 2011 8:31 AM by 836082 RSS

    ORA-00937: not a single-group group function

    DanCodi
      I am having a problem with this subquery getting ORA-00937: not a single-group group function can someone lend a hand?


      SELECT NVL(SUM(DECODE(CNM.MEDCIN_ID,-2001,1,0)),0) BMI,
      (SELECT NVL(Sum(DECODE(CNM.MEDCIN_ID,1009639,1,0)),0)
      FROM
      PAT_ENCOUNTER_MASTER PEM,
      CLINICAL_NOTE CN,
      CLINICAL_NOTE_MEDCIN CNM
      WHERE PEM.ENCOUNTER_ID = CN.ENCOUNTER_ID
      AND CN.CLINICAL_NOTE_ID = CNM.CLINICAL_NOTE_ID
      AND CNM.MEDCIN_ID =1009639
      AND CN.ENTITY_ID = 4921690) FUPD
      FROM
      PAT_ENCOUNTER_MASTER PEM,
      CLINICAL_NOTE CN,
      CLINICAL_NOTE_MEDCIN CNM
      WHERE PEM.ENCOUNTER_ID = CN.ENCOUNTER_ID
      AND CN.CLINICAL_NOTE_ID = CNM.CLINICAL_NOTE_ID
      AND CNM.MEDCIN_ID =-2001
      AND CN.ENTITY_ID =4921690
      AND CNM.VALUE >= 25
        • 1. Re: ORA-00937: not a single-group group function
          Frank Kulash
          Hi,

          When you use a GROUP BY clause and/or an aggregate fucntion, then everything in the SELECT clause must be:
          (A) an <b>A</b>ggregate function,
          (B) one of the "group <b>B</b>y" expressions,
          (C) a <b>C</b>onstant, or
          (D) something that <b>D</b>epends entirely on the above. (For example, if you "GROUP BY TRUNC(dt)", you can "SELECT TO_CHAR (TRUNC(dt), 'Mon-DD')").

          You are using an aggregate function in the main query, so the rules above apply to all the columns in the main SELECT clause, including fupd, which, as posted, does not fit into any of those categories.

          How to fix the problem depends on your data, and what results you want from that data.
          If you'd like help, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables. Simplify the problem as much as possible.
          Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
          Always say which version of Oracle you're using.
          • 2. Re: ORA-00937: not a single-group group function
            797687
            Curious :-)
            SELECT NVL (SUM (DECODE (CNM.MEDCIN_ID, -2001, 1, 0)), 0) BMI,
                   (SELECT   NVL(SUM (DECODE (CNM.MEDCIN_ID, 1009639, 1, 0)), 0)
                       FROM   PAT_ENCOUNTER_MASTER PEM,
                                  CLINICAL_NOTE CN,
                                  CLINICAL_NOTE_MEDCIN CNM
                     WHERE   PEM.ENCOUNTER_ID = CN.ENCOUNTER_ID
                          AND  CN.CLINICAL_NOTE_ID = CNM.CLINICAL_NOTE_ID
                          AND  CNM.MEDCIN_ID = 1009639
                          AND  CN.ENTITY_ID = 4921690) FUPD
                          
              FROM   PAT_ENCOUNTER_MASTER PEM, CLINICAL_NOTE CN, CLINICAL_NOTE_MEDCIN CNM
              
            WHERE   PEM.ENCOUNTER_ID = CN.ENCOUNTER_ID
                AND   CN.CLINICAL_NOTE_ID = CNM.CLINICAL_NOTE_ID
                AND   CNM.MEDCIN_ID = -2001
                AND   CN.ENTITY_ID = 4921690
                AND   CNM.VALUE >= 25
             
             GROUP BY (SELECT   NVL(SUM (DECODE (CNM.MEDCIN_ID, 1009639, 1, 0)), 0)
                       FROM   PAT_ENCOUNTER_MASTER PEM,
                                  CLINICAL_NOTE CN,
                                  CLINICAL_NOTE_MEDCIN CNM
                     WHERE   PEM.ENCOUNTER_ID = CN.ENCOUNTER_ID
                          AND  CN.CLINICAL_NOTE_ID = CNM.CLINICAL_NOTE_ID
                          AND  CNM.MEDCIN_ID = 1009639
                          AND  CN.ENTITY_ID = 4921690)
            • 3. Re: ORA-00937: not a single-group group function
              DanCodi
              NG.

              ORA-22818: subquery expressions not allowed here
              22818. 00000 - "subquery expressions not allowed here"
              *Cause:    An attempt was made to use a subquery expression where these
              are not supported.
              *Action:   Rewrite the statement without the subquery expression.
              Error at Line: 18 Column: 9
              • 4. Re: ORA-00937: not a single-group group function
                836082
                the best way to attack this is to take your scalar subquery out and use it in a factored subquery (a "WITH" clause). Try it like this:
                WITH subquery AS 
                    (SELECT NVL(Sum(DECODE(CNM.MEDCIN_ID,1009639,1,0)),0) AS fupd
                     FROM PAT_ENCOUNTER_MASTER PEM
                      ,   CLINICAL_NOTE CN
                      ,   CLINICAL_NOTE_MEDCIN CNM
                    WHERE PEM.ENCOUNTER_ID = CN.ENCOUNTER_ID
                      AND CN.CLINICAL_NOTE_ID = CNM.CLINICAL_NOTE_ID
                      AND CNM.MEDCIN_ID =1009639
                      AND CN.ENTITY_ID = 4921690     ) 
                SELECT NVL(SUM(DECODE(CNM.MEDCIN_ID,-2001,1,0)),0) BMI
                    ,   fupd
                FROM PAT_ENCOUNTER_MASTER PEM
                   ,  CLINICAL_NOTE CN
                   ,  CLINICAL_NOTE_MEDCIN CNM
                   ,  subquery
                WHERE PEM.ENCOUNTER_ID = CN.ENCOUNTER_ID
                   AND CN.CLINICAL_NOTE_ID = CNM.CLINICAL_NOTE_ID
                   AND CNM.MEDCIN_ID =-2001
                   AND CN.ENTITY_ID =4921690
                   AND CNM.VALUE >= 25
                GROUP BY fupd
                There is a difference in the way the query will work between my WITH clause and your scalar subquery. The way that I changed it, if the subquery returns no records, then the entire query will return no records. With your subquery, the field would just be NULL. So, if your subquery can possibly return no records, then you might want to look a little more.

                Also, this isn't part of the solution, just an additional observation I made while studying your query to re-format it: I wonder if you really those NVLs around the SUM/DECODE:
                NVL(SUM(DECODE(CNM.MEDCIN_ID,-2001,1,0)),0) 
                It seems to me that any NULLS in the medcin_id column are going to be changed to 0 by the DECODE function. Then, when you sum them, they will not be null.
                • 5. Re: ORA-00937: not a single-group group function
                  John Spencer
                  You can do this in a single pass through the table like:
                  SELECT COUNT(DECODE(cnm.medcin_id, -2001, 1)) bmi,
                         COUNT(DECODE(cnm.medcin_id, 1009639, 1)) fupd
                  FROM pat_encounter_master pem, clinical_note cn,
                       clinical_note_medcin cnm
                  WHERE pem.encounter_id = cn.encounter_id
                    AND cn.clinical_note_id = cnm.clinical_note_id
                    AND cnm.medcin_id in (1009639, -2001)
                    AND cn.entity_id = 4921690 
                    AND cnm.value >= DECODE(cnm.medcin_id, -2001, 25, cnm.value)
                  I changed your NVL(SUM( construct to a count to take advantage of the fact the aggregates will only operate on non-null values of the expression being aggregated, so COUNT will return 0 without the NVL if there are no matching records. I also think it is a clearer expression of intent, since you are actually counting not summing.

                  DECODE with no else part will return null if none of the values match the column being decoded.

                  John
                  • 6. Re: ORA-00937: not a single-group group function
                    DanCodi
                    Your the man, thanks a bunch. got it down to just a count now and will use SQL%NOTFOUND for null

                    WITH SUB AS
                    (SELECT Count(CNM.MEDCIN_ID)fupd
                    FROM PAT_ENCOUNTER_MASTER PEM
                    , CLINICAL_NOTE CN
                    , CLINICAL_NOTE_MEDCIN CNM
                    WHERE PEM.ENCOUNTER_ID = CN.ENCOUNTER_ID
                    AND CN.CLINICAL_NOTE_ID = CNM.CLINICAL_NOTE_ID
                    AND CNM.MEDCIN_ID =1009639
                    AND CN.ENTITY_ID = 4921690)
                    SELECT Count(CNM.MEDCIN_ID) BMI
                    , fupd
                    FROM PAT_ENCOUNTER_MASTER PEM
                    , CLINICAL_NOTE CN
                    , CLINICAL_NOTE_MEDCIN CNM
                    , sub
                    WHERE PEM.ENCOUNTER_ID = CN.ENCOUNTER_ID
                    AND CN.CLINICAL_NOTE_ID = CNM.CLINICAL_NOTE_ID
                    AND CNM.MEDCIN_ID =-2001
                    AND CN.ENTITY_ID =4921690
                    AND CNM.VALUE >= 25
                    GROUP BY FUPD
                    • 7. Re: ORA-00937: not a single-group group function
                      836082
                      Hi DanCodi:

                      I was wondering if you could take just a second and mark the question as answered for us? Oracle keeps score on how we're doing, whether or not we give correct answers to the questions posted, and I'm trying to climb out of "Newbie" status!

                      I would truly appreciate it. And, thanks for the challenging question...