Division par la somme d'un champ [Résolu]

[Norin]

Bonjour à tous,

J'essaie de créer une requête SQL me permettant de diviser un champ par sa sommation. Exemple:

Code :

1
2
3
4
5
6
Annee     Canal     Valeur
2011      01        1872
2010      01        2978
2009      01        3874
2011      02        2983
2010      02        1234

Je désire donc récupérer ces données:

Canal 01 - Année 2011 : 21,46%
Canal 01 - Année 2010 : 34,14%
Canal 01 - Année 2009 : 44,41%

Canal 02 - Année 2011 : 70,74%
Canal 02 - Année 2010 : 29,26%

Il s'agit donc de diviser la "Valeur" de chaque ligne par la sommation de la colonne "Valeur" groupée par année & canal.

Si quelqu'un pourrait m'aider, ce serait grandement apprécié !!

Merci

*NOTE: Je travail sous Access 2007...

*EDIT: Actuellement, mon code ressemble à cela:

Code :

1
2
3
4
SELECT T_EXCEL_Demande.NoAnnPre, T_PIL_Canaux.CodeCan, T_EXCEL_Demande.Total, Sum(T_EXCEL_Demande_1.Total) AS SommeDeTotal, IIf(T_EXCEL_Demande.Total=0,0,[T_EXCEL_Demande]![Total]/Sum([T_EXCEL_Demande_1]![Total])) AS Pourcent
FROM (T_EXCEL_Demande INNER JOIN T_EXCEL_Demande AS T_EXCEL_Demande_1 ON T_EXCEL_Demande.NoCan = T_EXCEL_Demande_1.NoCan) INNER JOIN T_PIL_Canaux ON T_EXCEL_Demande.NoCan = T_PIL_Canaux.NoCan
WHERE (((T_EXCEL_Demande.NoAnnPre)=0))
GROUP BY T_EXCEL_Demande.NoAnnPre, T_PIL_Canaux.CodeCan, T_EXCEL_Demande.Total;

Cependant, la colonne "Total" retourne des valeurs impossibles et plutôt que d'avoir ~100 occurrences, il m'en retourne ~1000.

[punkoff]

Bonsoir,


Vous vous y prenez à l'envers fait.

votre sous-requete va être le resultat de l'agrégation (somme des valeur par canaux).

Ce résultat va être pris pour faire votre division, en utilisant une jointure.


Donc essayez dans l'autre sens car vous n'êtes pas loin de la solution

[Norin]

Je crois bien avoir un soucis au niveau de la jointure, car je n'arrive même pas à exécuter ce code:

Code :

1
2
3
SELECT T_EXCEL_Demande.NoCan, T_EXCEL_Demande.NoAnnPre
FROM T_EXCEL_Demande INNER JOIN T_EXCEL_Demande AS T_EXCEL_Demande_1 ON T_EXCEL_Demande.NoCan = T_EXCEL_Demande_1.NoCan
ORDER BY T_EXCEL_Demande.NoCan, T_EXCEL_Demande.NoAnnPre;

Celui-ci me dit que je dépasse les 2 Gigs alloués -.-

[punkoff]

Faut voir que là vous faites une jointure inutile qui va dupliquer plein de donnée.

Donc même si vous arrivez aux limites de votre "SGBD" le résultat n'aurait pas été probant.

Je pensais plutôt à une solution de ce genre :

Code :

1
2
3
4
5
6
7
8
9
 
SELECT annee, nocan, valeur, valeur / sum_val * 100
FROM a_table a
INNER JOIN (
SELECT annee, nocan, sum(valeur) AS val_sum
FROM ma_table
WHERE noAnnPre = 0
GROUP BY anne, nocan) B ON a.annee = b.annee AND a.nocan = b.nocan
WHERE b.noAnnPre = 0

[boussafi]

Requete:

Code :

1
2
3
4
5
6
7
8
9
SELECT A.annee,A.NoCan,valeur/somme_valeur*100 
FROM la_table A 
JOIN
(SELECT sum(valeur)somme_valeur,NoCan 
FROM la_table 
GROUP BY NoCan)B 
ON
A.NoCan= B.NoCan
ORDER BY A.NoCan

Resulat:

Code :

1
2
3
4
5
6
   	   	ANNEE	NOCAN	pourcentage	
             2011	1	21,4580467675378
	2010	1	34,1357175607519
	2009	1	44,4062356717102
	2011	2	70,7374911074223
	2010	2	29,2625088925777

[Norin]

Bonjour,

après ma dernière réunion, le but a quelque peu changé mais le tout reste dans la même lignée... En fait, celui-ci veut obtenir le ratio de canal PAR ANNÉE... Exemple, canal 1: 90%, canal 2: 10% pour l'année 1, canal 1: 60%, canal 2: 40% pour l'année 2 etc... Ainsi, je dois obtenir la somme de valeurs de tous les canaux de l'année divisée par la somme de total d'un seul canal pour cette même année.

Comme le diviseur que vous m'aviez proposé représente le nombre divisé, j'ai tenté la requête suivante:

Code :

1
2
3
4
5
6
7
SELECT a.NoAnnPre, a.NoCan, Sum(a.Total) AS SommeDeTotal, sum(sum_val) AS SommeDesum_val
FROM T_EXCEL_Demande AS a INNER JOIN (SELECT  NoAnnPre, NoCan, sum(Total) AS sum_val
FROM T_EXCEL_Demande
WHERE noAnnPre = 0
GROUP BY NoAnnPre, NoCan)  AS b ON (a.NoCan = b.NoCan) AND (a.NoAnnPre = b.NoAnnPre)
WHERE (((b.NoAnnPre)=0))
GROUP BY a.NoAnnPre, a.NoCan;

Cependant, mon diviseur ne donne pas le bon résultat. Par exemple, je récupère la valeur 1589559120 pour le canal 1 plutôt que 19654974.

[punkoff]

Bonjour,

avez-vous compris le principe d'agrégation en SQL ?

Tenez : http://sqlpro.developpez.com/cours/sqlaz/ensembles/

Avec ceci et la requête de Boussafi (à modifier) je penses que vous devriez arriver à vos fin sans trop de souci.


Commencez par la sous-requête et réfléchissez aux jointures necessaires ..

[Norin]

Bonjour,

Je suis au regret de devoir vous décevoir, car je ne comprend pas d'où provient l'erreur... L'agrégation me semble correcte, je groupe par année et par canaux. Ma sous-requête (celle incluse dans le join) me retourne les bons résultats, tout ce que je fais dans ma requête originale est d'effectuer une somme des résultats de celle-ci.

Si je retire mon groupage par Année et Canaux, je reçois une erreur comme quoi ces expressions manquent à la fonction d'agrégat.

De plus, si j'essaie de diviser mes deux nombres (malgré que la somme des totaux ne soit pas bonne), je reçois une constante erronée...

Voici mon dernier code:

Code :

1
2
3
4
5
6
SELECT A.NoAnnPre, A.NoCan, Sum(A.total) AS SommeDeValeur, Sum(b.somme_valeur) AS SommeDeTotal, SommeDeValeur/SommeDeTotal AS Expr1
FROM T_EXCEL_Demande AS A 
INNER JOIN (SELECT sum(Total) AS somme_valeur,NoCan, NoAnnPre
FROM T_EXCEL_Demande
GROUP BY NoCan, NoAnnPre)  AS b ON (A.NoCan = b.NoCan) AND (A.NoAnnPre = b.NoAnnPre)
GROUP BY A.NoAnnPre, A.NoCan;

PS: La fonction "join" seule ne semble pas fonctionner sous Access

[skuatamad]

Envoyé par Norin

Je suis au regret de devoir vous décevoir, car je ne comprend pas d'où provient l'erreur...

C'est à cause de :

Code :

SommeDeValeur/SommeDeTotal AS Expr1

Tu n'as pas accès aux alias puisque tu es au même niveau.
Il faut le remplacer par les vrai sum :

Code :

1
2
3
4
5
SELECT A.NoAnnPre, A.NoCan, 
       Sum(A.total) AS SommeDeValeur, 
       Sum(b.somme_valeur) AS SommeDeTotal, 
       Sum(A.total)/Sum(b.somme_valeur) AS Expr1
...

Enfin ce que je dis concerne l'erreur, je n'ai pas particulièrement regardé la requette couplée au besoin pour savoir si cette division correspond...

[Edit]

Ainsi, je dois obtenir la somme de valeurs de tous les canaux de l'année divisée par la somme de total d'un seul canal pour cette même année.

Tu es sûr que ce n'est pas l'inverse ?

[Norin]

Envoyé par skuatamad

C'est à cause de :

Code :

SommeDeValeur/SommeDeTotal AS Expr1

Tu n'as pas accès aux alias puisque tu es au même niveau.
Il faut le remplacer par les vrai sum :

Code :

Enfin ce que je dis concerne l'erreur, je n'ai pas particulièrement regardé la requette couplée au besoin pour savoir si cette division correspond...

J'obtiens le même résultat... Je ne reçois pas une erreur à proprement dire, seulement un résultat faussé

Pour ce que j'ai à obtenir, je vais refaire un petit tableau car je trouve que ceux-ci sont beaucoup plus exhaustif:

Code :

1
2
3
4
5
6
Annee     Canal     Valeur     Pourcent
2011      01        1000       16,67% (1000 / (1000 + 2000 + 3000))
2011      02        2000       33,33%
2011      03        3000       50,00%
2010      01        4000       80,00%
2010      03        1000       20,00%

[skuatamad]

Regarde mon edit plus haut :

Tu es sûr que ce n'est pas l'inverse ?

[punkoff]

Code :

1
2
3
4
 
SELECT sum(Total) AS somme_valeur,NoCan, NoAnnPre
FROM T_EXCEL_Demande
GROUP BY NoCan, NoAnnPre

ceci est votre sous-requête.

Et le group by est incohérent par rapport à ce que vous demandez (j'en déduis que ma 1ere proposition ne devait pas marcher au mieux !)

=>

Code :

1
2
3
4
 
SELECT sum(Total) AS somme_valeur, NoAnnPre
FROM T_EXCEL_Demande
GROUP BY NoAnnPre

donc au final je ferai ça, qui doit coller avec votre demande..sinon reformulez votre demande

Code :

1
2
3
4
5
6
 
SELECT A.NoAnnPre, A.NoCan, a.valeur, valeur/somme_valeur * 100 AS Expr1
FROM T_EXCEL_Demande AS A 
INNER JOIN (SELECT sum(Total) AS somme_valeur, NoAnnPre
FROM T_EXCEL_Demande
GROUP BY  NoAnnPre)  AS b ON  (A.NoAnnPre = b.NoAnnPre)

edit : ceci a déjà été remonté par skuatamad, désolé
Sinon dans votre requête proposée il y a un problème dans le select :

Code :

1
2
3
4
5
6
7
8
9
10
 
SELECT A.NoAnnPre, A.NoCan, 
Sum(A.total) AS SommeDeValeur, 
Sum(b.somme_valeur) AS SommeDeTotal, 
SommeDeValeur/SommeDeTotal AS Expr1
FROM T_EXCEL_Demande AS A 
INNER JOIN (SELECT sum(Total) AS somme_valeur,NoCan, NoAnnPre
FROM T_EXCEL_Demande
GROUP BY NoCan, NoAnnPre)  AS b ON (A.NoCan = b.NoCan) AND (A.NoAnnPre = b.NoAnnPre)
GROUP BY A.NoAnnPre, A.NoCan;

En effet SommeDeValeur & SommeDeTotal ne sont définis qu'au résultat de votre requête et ne peuvent donc pas être réutilisés dans le même select ! (d'où votre erreur)
il faudrait pour cela refaire une sous-requête afin de faire le calcul que vous souhaitez

[boussafi]

remarque pour Norin, il manque les feedback des reponses de ton besoin

calcule de somme de valeur par année

Code :

1
2
3
SELECT sum(valeur)somme_valeur,annee 
FROM la_table 
GROUP BY annee

deviser chaque valeur d'une année donnée par la somme des valeurs de la meme année.

Code :

1
2
3
4
5
6
7
8
9
SELECT A.annee,A.canal,valeur/somme_valeur*100 
FROM la_table A 
JOIN
(SELECT sum(valeur)somme_valeur,annee 
FROM la_table 
GROUP BY annee)B 
ON
A.annee= B.annee
ORDER BY A.annee DESC

affichage du resulat:

Code :

1
2
3
4
5
6
   	ANNEE	CANAL	VALEUR/SOMME_VALEUR*100
	2011	1	16,6666666666667
	2011	2	33,3333333333333
	2011	3	50
	2010	3	20
	2010	1	80

ton feedkack j'attends

[Norin]

Bonjour,
Désolé pour le délai des feedbacks, je ne peux travailler sur cette requête que lorsque l'accès à la base de données est disponible.

Boussafi, j'ai essayé votre requête et celle-ci me retourne 46k résultats plutôt que 128.

Pour cette requête:

Code :

1
2
3
4
5
6
7
8
9
SELECT A.NoAnnPre, A.NoCan, 
Sum(A.total) AS SommeDeValeur, 
Sum(b.somme_valeur) AS SommeDeTotal, 
sum(total)/sum(somme_valeur) AS Expr1
FROM T_EXCEL_Demande AS A 
INNER JOIN (SELECT sum(Total) AS somme_valeur,NoCan, NoAnnPre
FROM T_EXCEL_Demande
GROUP BY NoCan, NoAnnPre)  AS b ON (A.NoCan = b.NoCan) AND (A.NoAnnPre = b.NoAnnPre)
GROUP BY A.NoAnnPre, A.NoCan;

Le résultat me retourne bel et bien 128 résultats, la colonne SommeDeValeur est exacte (elle retourne le bon nombre à être divisé) cependant le diviseur (SommeDeTotal) n'est guère le bon et le résultat dans Expr1 ne fait aucun sens car il est répété malgré les changements de SommeDeValeur et SommeDeTotal.

J'ai mis en pièce jointe une portion visible du résultat de la requête (resultat.JPG), le détail de la table T_EXCEL_Demande (table.JPG) ainsi que le résultat des totaux de valeurs (totaux.JPG).

Si l'on prend l'image contenant les totaux de valeurs, je désire obtenir, pour l'année 0 et le canal 1 par exemple, 4415442/(4415442 + 4880766 + 277128 + 600131 + 340191 + 2440883 + 1556296 + 5144137) donc, 22.46% séparés ainsi: année, canal (comme dans le fichier ).

En espérant être un peu plus clair sur mes besoins :S

Un très grand merci à tous !

[CinePhil]

J'ai un peu survolé la discussion mais, si je comprends bien, tu veux le pourcentage de chaque canal par année ?
Il ne faut pas inclure le canal dans le groupement de la sous-requête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT A.NoAnnPre, A.NoCan, 
    Sum(A.total) AS SommeDeValeur, 
    Sum(b.somme_valeur) AS SommeDeTotal, 
    sum(A.total)/sum(b.somme_valeur) AS Expr1
FROM T_EXCEL_Demande AS A 
INNER JOIN 
(
    SELECT sum(Total) AS somme_valeur, NoAnnPre
    FROM T_EXCEL_Demande
    GROUP BY NoAnnPre
)  AS b ON A.NoAnnPre = b.NoAnnPre
GROUP BY A.NoAnnPre, A.NoCan;

[Norin]

Envoyé par CinePhil

J'ai un peu survolé la discussion mais, si je comprends bien, tu veux le pourcentage de chaque canal par année ?
Il ne faut pas inclure le canal dans le groupement de la sous-requête :

Code :

Merci pour votre aide, cependant, le diviseur de votre requête pour l'année 0 par exemple retourne 7075790640 plutôt que 19654974

EDIT: OHMIGOD!

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT A.NoAnnPre, A.NoCan, 
    Sum(A.total) AS SommeDeValeur, 
    b.somme_valeur AS SommeDeTotal, 
    sum(A.total)/b.somme_valeur AS Expr1
FROM T_EXCEL_Demande AS A 
INNER JOIN 
(
    SELECT sum(Total) AS somme_valeur, NoAnnPre
    FROM T_EXCEL_Demande
    GROUP BY NoAnnPre
)  AS b ON A.NoAnnPre = b.NoAnnPre
GROUP BY A.NoAnnPre, A.NoCan, b.somme_valeur;

Cette requête fonctionne parfaitement ! Je ne comprends pas trop pourquoi, mais ça fonctionne !!! Merci à tous !

PS: Si quelqu'un peut m'expliquer, je suis preneur... Depuis 2 jours je fais surtout des essais à l'aveuglette et ça a finalement fonctionné

[CinePhil]

Tout simplement : la sous-requête calcule la somme de tous les canaux par année et la requête principale utilise la sous-requête comme diviseur pour avoir la proportion par année et par canal.

EDIT :
Effectivement, je n'avais pas vu qu'il ne fallait pas sommer le total par année !