Problème de group by

**Bishop00** · 12/02/2010, 11h42

Bonjour

voici ma requete:

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
SELECT
  ATELIER,
  RESS,
  decode(SEM2,-4,sum(TUTIL)/sum(TOUVMACHINE)*100) as a,
  decode(SEM2,-3,sum(TUTIL)/sum(TOUVMACHINE)*100) as b,
  decode(SEM2,-2,sum(TUTIL)/sum(TOUVMACHINE)*100) as c,
  decode(SEM2,-1,sum(TUTIL)/sum(TOUVMACHINE)*100) as d,
  decode(SEM2,0,sum(TUTIL)/sum(TOUVMACHINE)*100) as e
FROM( SELECT
      GR_TACHES.LIBELLE as ATELIER,
      TACHES.LIBELLE as RESS,
      extract(week from HORO_FIN) as SEM,
      extract(week from HORO_FIN)-extract(week from current_date)as SEM2,
      TRTRAV.TUTIL,
      TRTRAV.TOUVMACHINE
     FROM
      (TACHES INNER JOIN TRTRAV ON TACHES.COD_TACHE = TRTRAV.COD_TACHE) INNER JOIN GR_TACHES ON
       TACHES.GR_TACHE = GR_TACHES.GR_TACHE
     WHERE
       (((GR_TACHES.GR_TACHE)=1 Or (GR_TACHES.GR_TACHE)=2))and
       TRTRAV.TOUVMACHINE <>'0'and
       TRTRAV.TUTIL <>'0'and
       extract(year from HORO_FIN)=extract(year from current_date)and
       extract(week from HORO_FIN)> extract(week from current_date)-5
       and TACHES.LIBELLE='V20'
        )
GROUP BY
 ATELIER,
 RESS,
 SEM2
;

Le résultat

Code :

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

1
2
3
4
5
ATELIER	RESS	SEM2	A	B	C	D
INJ	V20	69				
INJ	V20		77			
INJ	V20			98		
INJ	V20				98

Ce que je voudrais

Code :

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

1
2
ATELIER	RESS	SEM2	A	B	C	D
INJ	V20	69	77	98	98

Merci.

**Cl@udius** · 12/02/2010, 12h02

Salut

Que renvoient SEM et SEM2 si tu les inclus au SELECT ?

@+ Claudius

**Bishop00** · 12/02/2010, 12h39

Envoyé par Cl@udius

Salut

Que renvoient SEM et SEM2 si tu les inclus au SELECT ?

@+ Claudius

J'ai supprimé SEM , j'ajoute SEM2 dans le select :

ATELIER_RESS_SEM2__A__B__C__D
INJ_____V20__-4____69
INJ_____V20__-3_______77
INJ_____V20__-2__________98
INJ_____V20__-1_____________98

et si j'enlève SEM2 du dernier group by, ce que j'aurais en sql oracle, j'ai un message d'erreur
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)

**eldrad95** · 19/02/2010, 14h53

C'est un peu bourrin comme truc et y a peu être une ou deux erreurs

mais qu'est ce que ça te donne ça ?

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
with t4 as (SELECT
      GR_TACHES.LIBELLE AS ATELIER,
      TACHES.LIBELLE AS RESS,
      extract(week FROM HORO_FIN) AS SEM,
      extract(week FROM HORO_FIN)-extract(week FROM current_date)AS SEM2,
      sum(TUTIL)/sum(TOUVMACHINE)*100) AS a
     FROM
      (TACHES INNER JOIN TRTRAV ON TACHES.COD_TACHE = TRTRAV.COD_TACHE) INNER JOIN GR_TACHES ON
       TACHES.GR_TACHE = GR_TACHES.GR_TACHE
     WHERE
       (((GR_TACHES.GR_TACHE)=1 OR (GR_TACHES.GR_TACHE)=2))AND
       TRTRAV.TOUVMACHINE <>'0'AND
       TRTRAV.TUTIL <>'0'AND
       extract(year FROM HORO_FIN)=extract(year FROM current_date)AND
       extract(week FROM HORO_FIN)> extract(week FROM current_date)-5
       AND TACHES.LIBELLE='V20' AND SEM2 = -4
        ),
		t3 as (SELECT
      GR_TACHES.LIBELLE AS ATELIER,
      TACHES.LIBELLE AS RESS,
      extract(week FROM HORO_FIN) AS SEM,
      extract(week FROM HORO_FIN)-extract(week FROM current_date)AS SEM2,
      sum(TUTIL)/sum(TOUVMACHINE)*100 AS b
     FROM
      (TACHES INNER JOIN TRTRAV ON TACHES.COD_TACHE = TRTRAV.COD_TACHE) INNER JOIN GR_TACHES ON
       TACHES.GR_TACHE = GR_TACHES.GR_TACHE
     WHERE
       (((GR_TACHES.GR_TACHE)=1 OR (GR_TACHES.GR_TACHE)=2))AND
       TRTRAV.TOUVMACHINE <>'0'AND
       TRTRAV.TUTIL <>'0'AND
       extract(year FROM HORO_FIN)=extract(year FROM current_date)AND
       extract(week FROM HORO_FIN)> extract(week FROM current_date)-5
       AND TACHES.LIBELLE='V20' AND SEM2 = -3
        )
		t2 as (SELECT
      GR_TACHES.LIBELLE AS ATELIER,
      TACHES.LIBELLE AS RESS,
      extract(week FROM HORO_FIN) AS SEM,
      extract(week FROM HORO_FIN)-extract(week FROM current_date)AS SEM2,
      sum(TUTIL)/sum(TOUVMACHINE)*100 AS c
     FROM
      (TACHES INNER JOIN TRTRAV ON TACHES.COD_TACHE = TRTRAV.COD_TACHE) INNER JOIN GR_TACHES ON
       TACHES.GR_TACHE = GR_TACHES.GR_TACHE
     WHERE
       (((GR_TACHES.GR_TACHE)=1 OR (GR_TACHES.GR_TACHE)=2))AND
       TRTRAV.TOUVMACHINE <>'0'AND
       TRTRAV.TUTIL <>'0'AND
       extract(year FROM HORO_FIN)=extract(year FROM current_date)AND
       extract(week FROM HORO_FIN)> extract(week FROM current_date)-5
       AND TACHES.LIBELLE='V20' AND SEM2 = -2
        ),
                        t1 as (SELECT
      GR_TACHES.LIBELLE AS ATELIER,
      TACHES.LIBELLE AS RESS,
      extract(week FROM HORO_FIN) AS SEM,
      extract(week FROM HORO_FIN)-extract(week FROM current_date)AS SEM2,
      sum(TUTIL)/sum(TOUVMACHINE)*100 AS d
     FROM
      (TACHES INNER JOIN TRTRAV ON TACHES.COD_TACHE = TRTRAV.COD_TACHE) INNER JOIN GR_TACHES ON
       TACHES.GR_TACHE = GR_TACHES.GR_TACHE
     WHERE
       (((GR_TACHES.GR_TACHE)=1 OR (GR_TACHES.GR_TACHE)=2))AND
       TRTRAV.TOUVMACHINE <>'0'AND
       TRTRAV.TUTIL <>'0'AND
       extract(year FROM HORO_FIN)=extract(year FROM current_date)AND
       extract(week FROM HORO_FIN)> extract(week FROM current_date)-5
       AND TACHES.LIBELLE='V20' AND SEM2 = -1
        ),
		t0 as (SELECT
      GR_TACHES.LIBELLE AS ATELIER,
      TACHES.LIBELLE AS RESS,
      extract(week FROM HORO_FIN) AS SEM,
      extract(week FROM HORO_FIN)-extract(week FROM current_date)AS SEM2,
      sum(TUTIL)/sum(TOUVMACHINE)*100 AS e
     FROM
      (TACHES INNER JOIN TRTRAV ON TACHES.COD_TACHE = TRTRAV.COD_TACHE) INNER JOIN GR_TACHES ON
       TACHES.GR_TACHE = GR_TACHES.GR_TACHE
     WHERE
       (((GR_TACHES.GR_TACHE)=1 OR (GR_TACHES.GR_TACHE)=2))AND
       TRTRAV.TOUVMACHINE <>'0'AND
       TRTRAV.TUTIL <>'0'AND
       extract(year FROM HORO_FIN)=extract(year FROM current_date)AND
       extract(week FROM HORO_FIN)> extract(week FROM current_date)-5
       AND TACHES.LIBELLE='V20' AND SEM2 = 0
        )
		select gr_taches.libelle as atellier, a, b, c, d, e 
		from gr.taches 
		inner join t4 on (gr_taches.libelle = t4.atellier)
		inner join t3 on (gr_taches.libelle = t3.atellier)
		inner join t2 on (gr_taches.libelle = t2.atellier)
		inner join t1 on (gr_taches.libelle = t1.atellier)
		inner join t0 on (gr_taches.libelle = t0.atellier)

Problème de group by

SQL Firebird

Vue hybride

Discussions similaires

Partager

Partager