Calcul partiel dans un groupage plus large [Résolu]

[perdeak]

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 :

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]

Bonjour,

Moi, je ferai 2 requêtes :

Code :

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 :

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]

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

[boussafi]

tu essayes ceci:

Code :

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]

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 :

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]

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]

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]

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

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

[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 !

[perdeak]

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

[perdeak]

Envoyé par boussafi

tu essayes ceci:

Code :

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

[perdeak]

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]

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

Code :

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]

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]

Voilà où j'en suis :

Code :

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]

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]

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]

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 :

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]

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 :

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 :

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]

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 :

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 ?