ORA-00979: N'est pas une expression GROUP BY

**Jinkas** · 20/03/2012, 09h30

Bonjour,

Je ne vois dans la requête suivante ce qui ne va pas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
    SELECT T1.COL1, SUM(T1.COL3), (SUM(T1.COL3) + (SELECT SUM(T2.COL2) FROM TABLE2 T2 WHERE T2.COL1 = T1.COL1 AND T2.COL2 = T1.COL2)) AS ALIAS1
     FROM TABLE1 T1
    WHERE T1.COL1 = VALEUR1
      AND T1.COL2 = VALEUR2
 GROUP BY T1.COL1

Lorsque je l'exécute, j'ai l'erreur suivante:

ORA-00979: N'est pas une expression GROUP BY
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
Erreur à la ligne 1, colonne 136

Merci,

**laurentschneider** · 20/03/2012, 09h37

tu ne groupes pas sur t1.col2.

essaye :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
  SELECT T1.COL1,
         SUM (T1.COL3),
         (  SUM (T1.COL3)
          + (SELECT SUM (T2.COL2)
               FROM TABLE2 T2
              WHERE T2.COL1 = T1.COL1 AND T2.COL2 = T1.COL2))
            AS ALIAS1
    FROM TABLE1 T1
   WHERE T1.COL1 = VALEUR1 AND T1.COL2 = VALEUR2
GROUP BY T1.COL1, T1.COL2;

**sweetasnz** · 20/03/2012, 09h44

Envoyé par Jinkas

Bonjour,

Je ne vois dans la requête suivante ce qui ne va pas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
    SELECT T1.COL1, SUM(T1.COL3), (SUM(T1.COL3) + (SELECT SUM(T2.COL2) FROM TABLE2 T2 WHERE T2.COL1 = T1.COL1 AND T2.COL2 = T1.COL2)) AS ALIAS1
     FROM TABLE1 T1
    WHERE T1.COL1 = VALEUR1
      AND T1.COL2 = VALEUR2
 GROUP BY T1.COL1

Lorsque je l'exécute, j'ai l'erreur suivante:

Merci,

Le group by statement doit être utilisé sur un champ spécifique et sur des fonctions de ce champ uniquement. Sinon tu as l'erreur 979 sous ORACLE.
ex :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select col1, col1*2, sum(col1) from tab1 group by col1;

sinon sur plusieurs champs :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select col1, col1*col2,col2, sum(col1) from tab1 group by col1,col2;

reprends la doc, c'est bien expliqué.

**laurentschneider** · 20/03/2012, 09h50

autrement, si tu ne veux pas grouper sur col2, alors tu supprimes la jointure sur col2

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
  SELECT T1.COL1,
         SUM (T1.COL3),
         (  SUM (T1.COL3)
          + (SELECT SUM (T2.COL2)
               FROM TABLE2 T2
              WHERE T2.COL1 = T1.COL1 ))
            AS ALIAS1
    FROM TABLE1 T1
   WHERE T1.COL1 = VALEUR1 AND T1.COL2 = VALEUR2
GROUP BY T1.COL1;

**laurentschneider** · 20/03/2012, 09h55

Envoyé par sweetasnz

reprends la doc, c'est bien expliqué.

ok, alors explique-moi ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
with table1 as (select 1 col1, 2 col2, 3 col3 , 1 valeur1, 2 valeur2 from dual),
  table2 as (select * from table1)
SELECT T1.COL1, SUM(T1.COL3), (SUM (T1.COL3)
  + (SELECT SUM(T2.COL2) FROM TABLE2 T2 WHERE T2.COL1 = T1.COL1 and t2.col2=t1.col2)) AS ALIAS1
FROM TABLE1 T1
WHERE T1.COL1 = VALEUR1 AND T1.COL2 = VALEUR2
GROUP BY T1.COL1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
      COL1 SUM(T1.COL3)     ALIAS1
---------- ------------ ----------
         1            3          5

PS: sans doute un bogue d'optimisation

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/* Formatted on 20.03.2012 09:57:29 (QP5 v5.203.12040.36147) */
SET ECHO ON autot on;
 
WITH table1
     AS (SELECT 1 col1,
                2 col2,
                3 col3,
                1 valeur1,
                2 valeur2
           FROM DUAL 
union all select 1,3,3,4,5 from dual where 1=0
           ),
     table2 AS (SELECT * FROM table1)
  SELECT T1.COL1,
         SUM (T1.COL3),
         (  SUM (T1.COL3)
          + (SELECT SUM (T2.COL2)
               FROM TABLE2 T2
              WHERE T2.COL1 = T1.COL1 and t2.col2=t1.col2))
            AS ALIAS1
    FROM TABLE1 T1
   WHERE T1.COL1 = VALEUR1 AND T1.COL2 = VALEUR2
GROUP BY T1.COL1;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Error at line 16
ORA-00979: not a GROUP BY expression

**Jinkas** · 20/03/2012, 10h20

Bonjour,

Comme j'ai besoin de faire une jointure sur t1.col2, j'ai du ajouter t1.col2 dans mon select et dans le group by et ça marche (pas besoin de l'ajouter dans le select ça suffit dans le group by comme vous le dites)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
   SELECT T1.COL1, T1.COL2, SUM(T1.COL3), (SUM(T1.COL3) + (SELECT SUM(T2.COL2) FROM TABLE2 T2 WHERE T2.COL1 = T1.COL1 AND T2.COL2 = T1.COL2)) AS ALIAS1
     FROM TABLE1 T1
    WHERE T1.COL1 = VALEUR1
      AND T1.COL2 = VALEUR2
 GROUP BY T1.COL1, T1.COL2

Merci beaucoup pour votre aide!

**Jinkas** · 20/03/2012, 10h58

Je voudrais savoir si le modèle de code suivant vous semble correcte et où devrais-je mettre le compteur pour connaître le nombre de lignes traitées?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
 
   CURSOR CUR1
   IS
    SELECT T1.COL1, T1.COL2, T1.COL3
      FROM TABLE1 T1, TABLE2 T2
     WHERE T1.COL1 = T2.COL1
       AND T2.COL2 = 'VALEUR1';
 
   CURSOR CUR2(P_COL1 TABLE1.COL1%TYPE, P_COL2 TABLE1.COL2%TYPE)
   IS
    SELECT T3.COL1, SUM(T3.COL3) AS ALIAS1
      FROM TABLE3 T3
     WHERE T3.COL1 = P_COL1
       AND T3.COL2 = P_COL2
  GROUP BY T3.COL1;
 
  COMPTEUR NUMBER := 0;
 
BEGIN
  COMPTEUR := COMPTEUR + 1;
 
  FOR REC1 IN CUR1
  LOOP
    BEGIN
      FOR REC2 IN CUR2(REC1.COL1, REC1.COL2)
      LOOP
        BEGIN
          IF REC2.ALIAS1 <> REC1.COL3 THEN
              --DBMS....
          END IF;
        EXCEPTION
          WHEN OTHERS THEN
          RAISE ;
        END;
      END LOOP;
    EXCEPTION
      WHEN OTHERS THEN
        RAISE ;
    END;
  END LOOP;

Merci,

**Waldar** · 20/03/2012, 11h53

Imbriquer des curseurs quand on peut faire une seule requête, il n'y a pas grand'chose de pire niveau performance.

**Jinkas** · 20/03/2012, 12h10

Si je n'avais pas des difficultés, je n'aurai pas demandé de l'aide!

Merci,

**sweetasnz** · 20/03/2012, 12h56

PS: sans doute un bogue d'optimisation

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH table1
     AS (SELECT 1 col1,
                2 col2,
                3 col3,
                1 valeur1,
                2 valeur2
           FROM DUAL 
union ALL SELECT 1,3,3,4,5 FROM dual WHERE 1=0
           ),
     table2 AS (SELECT * FROM table1)
  SELECT T1.COL1,
         SUM (T1.COL3),
         (  SUM (T1.COL3)
          + (SELECT SUM (T2.COL2)
               FROM TABLE2 T2
              WHERE T2.COL1 = T1.COL1 AND T2.COL2=T1.COL2))
            AS ALIAS1
    FROM TABLE1 T1
   WHERE T1.COL1 = VALEUR1 AND T1.COL2 = VALEUR2
GROUP BY T1.COL1,T1.COL2;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
      COL1 SUM(T1.COL3)     ALIAS1
---------- ------------ ----------
         1            3          5

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH table1
     AS (SELECT 1 col1,
                2 col2,
                3 col3,
                1 valeur1,
                2 valeur2
           FROM DUAL 
union ALL SELECT 1,3,3,4,5 FROM dual WHERE 1=0
           ),
     table2 AS (SELECT * FROM table1)
  SELECT T1.COL1,
         SUM (T1.COL3),
         (  SUM (T1.COL3)
          + (SELECT SUM (T2.COL2)
               FROM TABLE2 T2
              WHERE T2.COL1 = T1.COL1))
            AS ALIAS1
    FROM TABLE1 T1
   WHERE T1.COL1 = VALEUR1 AND T1.COL2 = VALEUR2
GROUP BY T1.COL1;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
      COL1 SUM(T1.COL3)     ALIAS1
---------- ------------ ----------
         1            3          5

en effet c'est pas si bien expliqué que ça ...

group by :
à priori les champs dans les clauses "where" jouent également un rôle ...
bref, c'est bizarre, car on a pas besoin de spécifier T1.COL3 alors que T1.COL2 est nécessaire.
je ne l'explique pas.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH table1
     AS (SELECT 1 col1,
                2 col2,
                3 col3,
                1 valeur1,
                2 valeur2
           FROM DUAL 
union ALL SELECT 1,3,3,4,5 FROM dual WHERE 1=0
           ),
     table2 AS (SELECT * FROM table1)
  SELECT T1.COL1,
         SUM (T1.COL3),
         (  SUM (T1.COL3)
          + (SELECT SUM (T2.COL2)
               FROM TABLE2 T2
              WHERE T2.COL1 = T1.COL1 AND T2.COL2=T1.COL2))
            AS ALIAS1
    FROM TABLE1 T1
   WHERE T1.COL1 = VALEUR1 AND T1.COL2 = VALEUR2
GROUP BY T1.COL1,T1.COL3,T1.COL2;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
      COL1 SUM(T1.COL3)     ALIAS1
---------- ------------ ----------
         1            3          5

**Jinkas** · 20/03/2012, 13h08

Merci pour votre aide mais je ne comprends pas ce que je dois faire.

**Mohamed.Houri** · 20/03/2012, 14h31

Intéressant!!!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
mhouri.world> desc t1
 Name              Null?    Type
 ----------------------------------------- 
 ID                         NUMBER
 N1                         NUMBER
 VC2                        VARCHAR2(10)
 VC3                        VARCHAR2(100)
 
mhouri.world> select
  2       t1.id
  3      ,sum(t1.n1)
  4      ,(sum(t1.n1) +
  5             (select
  6                   sum(t2.x1) x
  7              from  t2
  8              where t2.id  = t1.id
  9              and   t2.x1  = t1.n1
 10              )
 11        ) as alias1
 12  from   t1
 13   where t1.id   = 11
 14   and   t1.n1   = 707
 15   group by
 16         t1.id
 17        ;
            and   t2.x1  = t1.n1
                           *
ERROR at line 9:
ORA-00979: not a GROUP BY expression

Ok, il veut que je groupe par t1.n1 et bien je vais le faire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
mhouri.world> select
  2       t1.id
  3      ,sum(t1.n1)
  4      ,(sum(t1.n1) +
  5             (select
  6                   sum(t2.x1) x
  7              from  t2
  8              where t2.id  = t1.id
  9              and   t2.x1  = t1.n1
 10              )
 11        ) as alias1
 12  from   t1
 13   where t1.id   = 11
 14   and   t1.n1   = 707
 15   group by
 16         t1.id
 17        ,t1.n1
 18        ;
 
        ID SUM(T1.N1)     ALIAS1                                                
---------- ---------- ----------                                                
        11        707

Je change légèrement ma requête en ajoutant une clause supplémentaire sur une autre colonne provenant de t1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
mhouri.world> select
  2       t1.id
  3      ,sum(t1.n1)
  4      ,(sum(t1.n1) +
  5             (select
  6                   sum(t2.x1) x
  7              from  t2
  8              where t2.id  = t1.id
  9              and   t2.x1  = t1.n1
 10              and   t2.vc2 = t1.vc2
 11              )
 12        ) as alias1
 13  from   t1
 14   where t1.id   = 11
 15   and   t1.n1   = 707
 16   group by
 17         t1.id
 18        ,t1.n1
 19        ;
            and   t2.vc2 = t1.vc2
                           *
ERROR at line 10:
ORA-00979: not a GROUP BY expression

Ok, il veut que je groupe maitenant par t1.vc2 et bien je vais le faire aussi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
mhouri.world> select
  2       t1.id
  3      ,sum(t1.n1)
  4      ,(sum(t1.n1) +
  5             (select
  6                   sum(t2.x1) x
  7              from  t2
  8              where t2.id  = t1.id
  9              and   t2.x1  = t1.n1
 10              and   t2.vc2 = t1.vc2
 11              )
 12        ) as alias1
 13  from   t1
 14   where t1.id   = 11
 15   and   t1.n1   = 707
 16   group by
 17         t1.id
 18        ,t1.n1
 19        ,t1.vc2
 20        ;
 
        ID SUM(T1.N1)     ALIAS1                                                
---------- ---------- ----------                                                
        11        707

Et ainsi de suite. A chaque fois qu'une colonne de la table t1(autre que t1.id) est invoquée dans le select imbriqué, je dois grouper par cette colonne

Une explication?

Peut-être en observant le plan d'exécution

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
mhouri.world> select * from table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
 
SQL_ID  5mzq0bmusjvtg, child number 0
-------------------------------------
select      t1.id     ,sum(t1.n1)     ,(sum(t1.n1) +            (select
           sum(t2.x1) x             from  t2             where t2.id  = t1.id
            and   t2.x1  = t1.n1             )       ) as alias1 from   t1
where t1.id   = 11  and   t1.n1   = 707  group by        t1.id       ,t1.n1
 
Plan hash value: 508850092
 
--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE              |       |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |    10 |   103   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T2_I1 |   100 |       |     3   (0)| 00:00:01 |
|   4 |  SORT GROUP BY NOSORT        |       |     1 |     9 |     3   (0)| 00:00:01 |
|*  5 |   INDEX RANGE SCAN           | T1_N1 |     1 |     9 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("T2"."ID"=:B1)
   3 - access("T2"."X1"=:B1)
   5 - access("T1"."ID"=11 AND "T1"."N1"=707)

Je vois que le CBO sélectionne deux informations de la table t1 (id et n1) et pas uniquement n1 sur les quelles il applique le predicat 5 et transmet le resulat en ''bind variable'' (:B1) à la table T2(access("T2"."X1"=:B1)

C'est donc peut-être à cause de cela qu'il a besoin d'un group by sur n1.

**mnitu** · 20/03/2012, 14h47

Envoyé par Mohamed.Houri

...
Une explication?
...

Une sous-requête scalaire remplace un Outer Join. Essayez de réécrire la requête avec l’outer join.

**Jinkas** · 20/03/2012, 15h26

Je ne sais plus où j'en suis:

tout ce que je demande c'est pouvoir optimiser mon code et résoudre mon problème du sum (alias total) qui j'ai l'impression ne prends pas en compte
(SUM(T3.COL3) dans total

ci-dessous les curseurs:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
  CURSOR CUR1
  IS
    SELECT T1.COL1, T1.COL2, T1.COL3
      FROM TABLE1 T1, TABLE2 T2
     WHERE T1.COL1 = T2.COL1
       AND T2.COL = VALEUR;
 
  CURSOR CUR2(P_COL1 TABLE1.COL1%TYPE, P_COL2 TABLE1.COL2%TYPE)
  IS
   SELECT T3.COL1, SUM(T3.COL3), (SUM(T3.COL3) + (SELECT SUM(T4.COL3) FROM TABLE4 T4 WHERE T4.COL1 = T3.COL1 AND T4.COL2 = T3.COL2)) AS TOTAL
     FROM TABLE3 L
    WHERE T3.COL1 = VALEUR
      AND T3.COL2 = VALEUR
 GROUP BY T3.COL1, T3.COL2

merci,

**laurentschneider** · 20/03/2012, 15h42

ça reste un bogue du parseur qui dépend du plan. En simplifiant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
WITH T AS (SELECT /*+ MATERIALIZE */ 1 x FROM DUAL)
SELECT 
   (SELECT 1 FROM dual WHERE 1=x) 
FROM T GROUP BY ();

retourne une erreur, mais si tu enlèves le group by, alors tu enlèves l'erreur!

**Jinkas** · 20/03/2012, 16h51

Sincèrement je ne sais pas si on se comprend?

**mnitu** · 20/03/2012, 17h19

Envoyé par laurentschneider

ça reste un bogue du parseur qui dépend du plan. En simplifiant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
WITH T AS (SELECT /*+ MATERIALIZE */ 1 x FROM DUAL)
SELECT 
   (SELECT 1 FROM dual WHERE 1=x) 
FROM T GROUP BY ();

retourne une erreur, mais si tu enlèves le group by, alors tu enlèves l'erreur!

C'est la (non)transformation de la requête qui provoque le bug

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
WITH T AS (SELECT /*+ no_merge */ 1 x FROM DUAL )
SELECT  
   (SELECT 1 FROM dual WHERE 1=x) 
FROM T GROUP BY ()

Ou

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
WITH T AS (SELECT  1 x FROM DUAL )
SELECT --+ NO_QUERY_TRANSFORMATION
   (SELECT 1 FROM dual WHERE 1=x) 
FROM T GROUP BY ()

**laurentschneider** · 20/03/2012, 18h29

Envoyé par mnitu

C'est la (non)transformation de la requête qui provoque le bug

Le bug c'est quand il n'y a pas d'erreur... mais bon, c'est pas un vrai bug, plutôt une bizarerie

**Jinkas** · 20/03/2012, 18h37

Excusez moi de vous déranger, je ne sais plus si je suis bien sur la discussion que j'ai créee?

**mnitu** · 20/03/2012, 19h02

Envoyé par Jinkas

Excusez moi de vous déranger, je ne sais plus si je suis bien sur la discussion que j'ai créee?

Pourriez-vous ajouter un petit jeu d'essai ainsi que le résultat que vous attendez pour qu'on arrive à mieux comprendre ce que vous voulez dire par

tout ce que je demande c'est pouvoir optimiser mon code et résoudre mon problème du sum (alias total) qui j'ai l'impression ne prends pas en compte
(SUM(T3.COL3) dans total

ORA-00979: N'est pas une expression GROUP BY

SQL Oracle

Discussions similaires

Partager

Partager