Calcul partiel dans un groupage plus large

**perdeak** · 28/08/2011, 23h14

Bonjour,

Voilà j'ai épuisé toutes mes forces dans cette requête et je ne trouve pas de réponse, j'aimerais avoir votre aide pour sortir ce que je souhaite de cette maudite requête.
Voilà ma requête (en rouge ce qui ne vas pas) :

Code :

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

1
2
3
4
5
6
7
8
9
10
select 
code_famille as "Code Famille",
texte_famille as "Texte Famille",
count(*) as Nombre,
sum(montant) as Montant,
sum(montant)/(select sum(montant)/100 from test) as "%/Total",
(select sum(montant) from test where (extract(MONTH from date)= '01' or extract(MONTH from date)= '02' or extract(MONTH from date)= '03')) as "1erTrimestre" --group by code_famille
from test 
group by code_famille, texte_famille 
order by montant desc

Je m'explique, dans ma colonne "1er trimestre" j'obtiens le total de tous mes montant du 1er trimestre, alors que je les voudrais classé par "code_famille", mais quand j'utilise mon group by, il me dit que plusieurs valeurs sont renvoyées....

Je ne sais plus trop quoi faire, merci pour votre aide.

**Rei Angelus** · 29/08/2011, 09h42

Bonjour,

Moi, je ferai 2 requêtes :

Code :

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

1
2
3
4
select code_famille, sum(montant) as montant
from test 
where (extract(MONTH from date)= '01' or extract(MONTH from date)= '02' or extract(MONTH from date)= '03')
group by code_famille

Je stockerai le résultat dans une table temporaire.

Puis

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
select 
test.code_famille as "Code Famille",
texte_famille as "Texte Famille",
count(*) as Nombre,
sum(test.montant) as Montant,
sum(test.montant)/(select sum(test.montant)/100 from test) as "%/Total",
table_tmp.montant  as "1erTrimestre"
from test
     join table_tmp
           on test.code_famille  = table_tmp.code_famille
group by test.code_famille, texte_famille, table_tmp.montant
order by test.montant desc

**Yanika_bzh** · 29/08/2011, 09h47

Pourquoi passer par une table temporaire alors que vous avez ecrit vos 2 requetes... une jointure suffirait

**islamov2000** · 29/08/2011, 10h49

tu essayes ceci:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT A.code_famille AS "Code Famille",A.texte_famille AS "Texte Famille",count(*) AS Nombre,sum(A.montant) AS Montant,sum(A.montant)/(sum(A.montant)/100 FROM test) AS "%/Total",
B.montant  AS "1erTrimestre"
FROM test A
     JOIN (
           select code_famille,sum(montant) 
           from test
           where (extract(MONTH from date)= '01' 
           or extract(MONTH from date)= '02' 
           or extract(MONTH from date)= '03')
           group by code_famille
           )B
           ON A.code_famille  = B.code_famille
GROUP BY A.code_famille, A.texte_famille, B.montant
ORDER BY A.montant DESC

**CinePhil** · 29/08/2011, 11h00

Il y a plus simple pour sélectionner dans le premier trimestre si la colonne date (qui ne devrait pas s'appeler ainsi car c'est un mot réservé du langage SQL) est de type DATE :

Code :

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

WHERE EXTRACT(MONTH FROM colonne_date) BETWEEN 1 AND 3

En plus, sauf erreur de ma part, et tel que je l'ai mis dans mon extrait de requête, EXTRACT(MONTH FROM colonne_date) d'une date retourne le numéro du mois sous forme d'entier.

**Rei Angelus** · 29/08/2011, 12h06

Envoyé par Yanika_bzh

Pourquoi passer par une table temporaire alors que vous avez ecrit vos 2 requetes... une jointure suffirait

Il s'agit d'une convenance personnelle : je ne suis pas partisan de l'imbrication des SELECT. Je trouve que cela nuit à la clarté.

**al1_24** · 29/08/2011, 12h13

Dommage, en effectuant une jointure entre vos deux requêtes, vous permettriez à l'optimiseur de votre SGBD de choisir la manière la plus adaptée de traiter votre requête et éventuellement d'en simplifier l'exécution.
En passant par une table intermédiaire, vous perdez cet avantage.

**Rei Angelus** · 29/08/2011, 12h31

Effectivement, j'avoue tout : cela court-circuite l'optimiseur.

Difficile de préserver à la fois mon cerveau et l'optimiseur.

**CinePhil** · 29/08/2011, 14h22

Petit détail que j'ai omis tout à l'heure : la sélection d'un trimestre avec votre méthode sur la sélection du mois fonctionne si vous n'avez les données que d'une année civile sinon vous aurez votre calcul pour tous les trimestres de toutes les années saisies en BDD !

**perdeak** · 29/08/2011, 19h27

Merci à tous pour vos réponses, je teste ça ce soir dès mon retour et vous tiens au courant

**perdeak** · 29/08/2011, 20h55

Envoyé par boussafi

tu essayes ceci:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT A.code_famille AS "Code Famille",A.texte_famille AS "Texte Famille",count(*) AS Nombre,sum(A.montant) AS Montant,sum(A.montant)/(sum(A.montant)/100 FROM test) AS "%/Total",
B.montant  AS "1erTrimestre"
FROM test A
     JOIN (
           select code_famille,sum(montant) 
           from test
           where (extract(MONTH from date)= '01' 
           or extract(MONTH from date)= '02' 
           or extract(MONTH from date)= '03')
           group by code_famille
           )B
           ON A.code_famille  = B.code_famille
GROUP BY A.code_famille, A.texte_famille, B.montant
ORDER BY A.montant DESC

Le problème c'est que mon requeteur ne reconnait pas la "table" B...

**perdeak** · 29/08/2011, 20h56

Envoyé par CinePhil

Petit détail que j'ai omis tout à l'heure : la sélection d'un trimestre avec votre méthode sur la sélection du mois fonctionne si vous n'avez les données que d'une année civile sinon vous aurez votre calcul pour tous les trimestres de toutes les années saisies en BDD !

Il me faut le cumul par trimestre de toutes les années de la table

**Yanika_bzh** · 30/08/2011, 09h24

Dans ce cas, il faudra etoffer un peu plus la requete, genre :

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
SELECT 
  B.code_famille,
  B.Texte_famille,
  A.NbParTrimestre,
  SUM(B.montant),
  A.montant / (SUM(B.montant) /100) as '%/Total',
  A.Trimestre,
  A.Annee,
  A.montant
FROM
  (SELECT              /* Groupement par trimestres et années */
    Code_famille, 
    SUM (montant) as montant
    CASE
	WHEN extract(MONTH FROM date) in (1,2,3) THEN 1
	WHEN extract(MONTH FROM date) in (4,5,6) THEN 2
	WHEN extract(MONTH FROM date) in (7,8,9) THEN 3
	ELSE 4 END as Trimestre ,
    extract(YEAR FROM date) as Annee,
    COUNT(1) as NbParTrimestre
  FROM
    test
  GROUP BY
    Code_famille,
    CASE
	WHEN extract(MONTH FROM date) in (1,2,3) THEN 1
	WHEN extract(MONTH FROM date) in (4,5,6) THEN 2
	WHEN extract(MONTH FROM date) in (7,8,9) THEN 3
	ELSE 4 END as Trimestre ,
    extract(YEAR FROM date) as Annee
 ) A INNER JOIN test B
ON (A.code_famille = B.code_famille)
GROUP BY
  B.code_famille,
  B.Texte_famille,
  A.NbParTrimestre,
  A.Trimestre,
  A.Annee,
  A.montant

A tester et a adapter !

Bon courage

**perdeak** · 30/08/2011, 18h40

Désolé j'ai dû mal m'exprimer, en fait dans ma colonne 1er trimestre, il me faut le cumul des montants de toutes mes commandes ayant eu lieu au premier trimestre quelle que soit l'année. Mais merci quand même.
En revanche je reste bloqué, en effet il me dit que la colonne B.montant n'existe pas, pourtant elle est définie dans le JOIN....

**perdeak** · 30/08/2011, 22h05

Voilà où j'en suis :

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
select
	global.code_famille as "Code Famille",
	global.texte_famille as "Texte Famille",
	count(*) as "Nombre",
	sum(global.montant) as "Montant",
	round(sum(global.montant)/(select sum(global.montant)/100 from test),2) as "%/Total",
	premierTrimestre.montant as "1erTrimestre"
From test global
 
Join (
	Select code_famille, sum(montant)
	From test
	Where (extract(MONTH from date)= '01'
	Or extract(MONTH from date)= '02'
	Or extract(MONTH from date)= '03')
	Group by code_famille
 
	) premierTrimestre
 
	ON global.code_famille = premierTrimestre.code_famille
 
group by global.code_famille, global.texte_famille, premierTrimestre.montant 
 
order by global.montant desc

Mais quand l'exécute voici le message d'erreur :

ERREUR: la colonne premiertrimestre.montant n'existe pas
LINE 7: premierTrimestre.montant as "1erTrimestre"
^

********** Erreur **********

ERREUR: la colonne premiertrimestre.montant n'existe pas
État SQL :42703
Caractère : 236

Quelqu'un a-t-il une idée, je sèche totalement....

**CinePhil** · 30/08/2011, 22h12

premierTrimestre est l'alias (un peu long pour un alias !) de la sous-requête après le JOIN et, effectivement, dans cette sous-requête, tu as SUM(montant) mais pas "montant". Il te manque un alias AS montant après le SUM(montant).

**perdeak** · 30/08/2011, 22h36

Effectivement ça me résout un problème mais pour en faire apparaître un autre, que je ne comprend pas...
ERREUR: la colonne « global.montant » doit apparaître dans la clause GROUP BY ou doit être utilisée dans une fonction d'agrégat

Si je rajoute mon global.montant dans mon Group By, la aussi j'ai une erreur :
ERREUR: plus d'une ligne renvoyée par une sous-requête utilisée comme une expression

**CinePhil** · 31/08/2011, 09h54

Revenons au besoin exprimé :

Je m'explique, dans ma colonne "1er trimestre" j'obtiens le total de tous mes montant du 1er trimestre, alors que je les voudrais classé par "code_famille"

Il me faut le cumul par trimestre de toutes les années de la table

Désolé j'ai du mal m'exprimer, en fait dans ma colonne 1er trimestre, il me faut le cumul des montants de toutes mes commandes ayant eu lieu au premier trimestre quelque soit l'année.

Donc, d'après ce qui précède et d'après votre requête, vous voulez, quelle que soit l'année et par famille :
- le code et le texte de la famille ;
- le nombre de commandes ;
- la somme des montants des commandes ;
- le pourcentage de cette somme par rapport à la somme de toutes les commandes ;
- le montant des commandes passées au cours d'un premier trimestre.

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
SELECT 
    t.code_famille AS "Code Famille",
    t.texte_famille AS "Texte Famille",
    COUNT(t.*) AS Nombre,
    SUM(t.montant) AS Montant,
    SUM(t.montant)/(SELECT SUM(montant)/100 FROM test) AS "%/Total",
    t1.montant_trim_1 AS "Montant 1er trimestre"
FROM test t
INNER JOIN
(
    SELECT code_famille, 
        SUM(montant) AS montant_trim_1
    FROM test 
    WHERE EXTRACT(MONTH FROM date) BETWEEN 1 AND 3
    GROUP BY code_famille
) AS t1 ON t1.code_famille = t.code_famille
GROUP BY code_famille, texte_famille 
ORDER BY SUM(t.montant) DESC

**estofilo** · 31/08/2011, 15h30

A moins que je n'aie pas compris le problème, il me semble qu'il y avait juste un détail à changer dans la requête telle que postée initialement.
En effet la sous-requête qui calcule la somme du 1er trimestre considère tous les code_famille alors qu'elle devrait considérer uniquement celui de la ligne "en cours" au niveau supérieur.
Si c'est bien ça, il suffit d'ajouter une clause d'appariement sur code_famille dans le where.

C'est-à-dire en remplacement de:

Code :

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

1
2
(select sum(montant) from test 
  where (extract(MONTH from date)= '01' or extract(MONTH from date)= '02' or extract(MONTH from date)= '03')) as "1erTrimestre"

faire plutôt:

Code :

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

1
2
3
4
(select sum(montant) from test TEST1 where 
  TEST1.code_famille=test.code_famille
  AND (extract(MONTH from date)= '01' or extract(MONTH from date)= '02' or extract(MONTH from date)= '03'))
  as "1erTrimestre"

**perdeak** · 31/08/2011, 22h00

Envoyé par CinePhil

Revenons au besoin exprimé :

Donc, d'après ce qui précède et d'après votre requête, vous voulez, quelle que soit l'année et par famille :
- le code et le texte de la famille ;
- le nombre de commandes ;
- la somme des montants des commandes ;
- le pourcentage de cette somme par rapport à la somme de toutes les commandes ;
- le montant des commandes passées au cours d'un premier trimestre.

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
SELECT 
    t.code_famille AS "Code Famille",
    t.texte_famille AS "Texte Famille",
    COUNT(t.*) AS Nombre,
    SUM(t.montant) AS Montant,
    SUM(t.montant)/(SELECT SUM(montant)/100 FROM test) AS "%/Total",
    t1.montant_trim_1 AS "Montant 1er trimestre"
FROM test t
INNER JOIN
(
    SELECT code_famille, 
        SUM(montant) AS montant_trim_1
    FROM test 
    WHERE EXTRACT(MONTH FROM date) BETWEEN 1 AND 3
    GROUP BY code_famille
) AS t1 ON t1.code_famille = t.code_famille
GROUP BY code_famille, texte_famille 
ORDER BY SUM(t.montant) DESC

En modifiant 2, 3 petites choses ça marche, je te remercie beaucoup.

J'ai encore juste un problème, j'ai un trimestre où il n'y a aucun montant, donc mon WHERE EXTRACT(MONTH FROM date) BETWEEN 1 AND 3 ne se déclenche jamais, du coup il me zap la ligne entière de ce dossier. J'ai bien vu qu'on pouvait faire du EXIST, mais est-ce possible sur le résultat d'un WHERE, ça renvoie FALSE si il ne trouve rien ?

Calcul partiel dans un groupage plus large

Langage SQL

Discussions similaires

Partager

Partager