Discussion :

[Slumpy]

Bonjour,

Je n arrive pas a me sortir de mon probleme desole. Comme vous pouvez le voir, dans le screenshot que j ai mis en fichier joint, j ai quatres tables qui sont en relations. J essaye de comprendre comment je pourrais faire une query permettant d obtenir le score de chaque eleve par module (donc une moyenne sur les assessments*leurpourcentage associe) et faire une query qui determine la moyenne de l etudient sur toute la formation (donc en fait, multiplier le resultqt de la query precedente par le coefficient de chaque module et de sommer le resultat sur tout les modules.

(bien sur pour plus de facilite, mon attribut Weight qui represente le poids de chaque Assessment et l attribut Coefficient qui represente lui le poids de chaque module dqns a formation sont du style : si c 30% => 0.3)

Je ne c pas si mon probleme est clair.

Pour calculer l apport de chaque assessment a la note finale sur le module, je pense au il me faut faire ca et encore suis pas sur:

Code :

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

1
2
3
4
 
SELECT SAM.Score * A.Weight AS Influence_Per_Assessment[/FONT]
FROM tblAssessment AS A, tblStudentAssessmentModule AS SAM
WHERE SAM.AssessmentID = A.AssessmentID;

Mais ensuite me faut faire la somme de ce resultat sur chaque Assessment du module (donc utiliser un GROUP BY SAM.StudentID, SAM.ModuleID) mais je ne vois pas concretement comment l ecrire.

ENfin voila, je galere. Si quelqu un pouvait m aider. Bien sur vous l aurez compris, je debute dans les Queries.

[Slumpy]

Pour la moyenne pour chaque eleve sur chaque module

Code :

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

1
2
3
4
5
6
7
 
SELECT SAM.StudentID, SAM.ModuleID, SUM(
    SELECT SAM.Score * A.Weight AS Influence_Per_Assessment
    FROM tblAssessment AS A, tblStudentAssessmentModule AS SAM
    WHERE SAM.AssessmentID = A.AssessmentID) AS Average_Per_Module
FROM tblStudentAssessmentModule AS SAM
GROUP BY SAM.StudentID, SAM.ModuleID;

Je fais totalement fausse route en reflechissant comme ca ?
Merci

[Swoög]

Alors, si je comprends bien, il te faut deux requêtes :

la première te renvoie :
Students.*, Module.*, SOMME(SAM.Note*Assessment.Weight)

pour chaque étudiant et module

et la seconde :
Students.*, SOMME(SOMME(SAM.Note*Assessment.Weight)*Module.Coeficient)/SOMME(Module.Coeficient)


?

Dans ce cas, je pense qu'il te faudra passer par les vues ou par une table temporaire pour plus de simplicité
Sinon il faudra passer par une sous-requête...

Je vais essayer de te proposer les deux solutions

Avec Table Temporaire ou Vue

Table temporaire ou Vue :
StudentID, ModuleID, Average

requête 1 (qui remplie la table temporaire, ou la vue) :

Code :

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

1
2
3
4
SELECT SAM.StudentID, SAM.ModuleID, SUM(SAM.Score * A.Weight) AS Average
FROM tblStudentAssessmentModule AS SAM
<div style="margin-left:40px">INNER JOIN tblAssessment AS A
<div style="margin-left:40px">ON SAM.AssessmentID = A.AssessmentID</div></div>GROUP BY SAM.StudentID, SAM.ModuleID

requête 2 :

Code :

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

1
2
3
4
SELECT TT.StudentID, SUM(TT.Average * M.Coeficient)/SUM(M.Coeficient) As Average
FROM tblTemp AS TT
<div style="margin-left:40px">INNER JOIN tblModule AS M
<div style="margin-left:40px">ON TT.ModuleID = M.ModuleID</div></div>GROUP BY TT.StudentID

Voici la version avec sous requête (donc seulement requête 2, pas de table temporaire) :

Code :

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

1
2
3
4
5
6
7
8
SELECT SAM.StudentID, SUM(
<div style="margin-left:40px">(SELECT SUM(SAM2.Score * A.Weight)
FROM tblStudentAssessmentModule AS SAM2
<div style="margin-left:40px">INNER JOIN tblAssessment AS A
<div style="margin-left:40px">ON SAM2.AssessmentID = A.AssessmentID</div></div>WHERE SAM2.StudentID = SAM.StudentID AND SAM2.ModuleID = M.ModuleID)</div>* M.Coeficient)/SUM(M.Coeficient) As Average
FROM tblStudentAssessmentModule AS SAM
<div style="margin-left:40px">INNER JOIN tblModule AS M
<div style="margin-left:40px">ON SAM.ModuleID = M.ModuleID</div></div>GROUP BY SAM.StudentID

[Slumpy]

Merci de m aider Swoög, c super sympa.

Tu as tres bien compris mes requetes.

Par contre pour pas rester con, j aurais quelques questions.

1) dans ton analyse de la 2ieme requete, tu consideres les coefficients comme etant 7, 8 ... des nombres quoi et pqs des pourcentqges non ? Si oui, ca m arrange, j avais abandonne cette option pour plus de simplicite.

2) Tu utilises un join dans la premiere requete, pour preparer le terrain pour la deuxieme non ? Car juste pour repondre a la premiere requete, mon code etait bon non ?

Code :

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

1
2
3
4
5
6
7
 
<div style="text-align: left;">SELECT SAM.StudentID, SAM.ModuleID, SUM(
    SELECT SAM.Score * A.Weight AS Influence_Per_Assessment
    FROM tblAssessment AS A, tblStudentAssessmentModule AS SAM
    WHERE SAM.AssessmentID = A.AssessmentID) AS Average_Per_Module
FROM tblStudentAssessmentModule AS SAM
GROUP BY SAM.StudentID, SAM.ModuleID;</div>

3) Je comprend ta premiere solution pour la premiere solution de la deuxieme requete, sauf aue je c pas d ou sort le tblTemp. Il est genere automatiquement a l issu de la premiere requete ?
(desole j ai pas encore etudier les vues)


Ta deuxieme solution pour la deuxieme requete vas me demander un peu plus de temps pour la saisir.
Quoi qu il en soit encore merci de m aider Swoög, ca a du te demander un peu de temps.

[Swoög]

alors

1) dans ton analyse de la 2ieme requete, tu consideres les coefficients comme etant 7, 8 ... des nombres quoi et pqs des pourcentqges non ? Si oui, ca m arrange, j avais abandonne cette option pour plus de simplicite.

par pourcentages... tu veux dire que la somme est toujours égale à 1 ? il n'y a pas besoin de diviser par la somme des coeficients ?

dans ce cas, il te suffit de retirer la partie "/SUM(M.Coeficient)" de la requête

2) Tu utilises un join dans la premiere requete, pour preparer le terrain pour la deuxieme non ? Car juste pour repondre a la premiere requete, mon code etait bon non ?

non, le join est là pour remplacer la sous-requête (c'est meilleur niveau performances, et plus clair niveau sémantique )

ta requête part sur une bonne idée, sauf quelques points :

• deux tables ont le même alias
• il te faut de toutes façons une jointure dans la sous-requête
• tu n'établies aucune correspondances entre la requête principale et la sous-requête alors qu'il y en a une
• le groupage se fait en tant que filtrage dans la sous-requête : c'est la relation de correspondance entre les deux requêtes

Voici une version corrigée :

Code :

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

1
2
3
4
SELECT SAM.StudentID, SAM.ModuleID, SUM(
<div style="margin-left:40px">SELECT SAM2.Score * A.Weight AS Influence_Per_Assessment
FROM tblStudentAssessmentModule AS SAM2 <div style="margin-left:40px">INNER JOIN  tblAssessment AS A <div style="margin-left:40px">ON A.AssessmentID = SAM2.AssessmentID</div></div>WHERE SAM2.StudentID = SAM.StudentID AND SAM2.ModuleID = SAM.ModuleID</div>) AS Average_Per_Module
FROM tblStudentAssessmentModule AS SAM

Comme tu le vois, tu as ici SOUS-REQUETE + JOINTURE dans la SSREQ + WHERE
au lieu de JOINTURE + GROUP BY
D'ailleurs cette requête se rapproche un peu de celle que je t'ai proposé pour la seconde requête dans le cas où il n'y a pas de table temporaire

3) Je comprend ta premiere solution pour la premiere solution de la deuxieme requete, sauf aue je c pas d ou sort le tblTemp. Il est genere automatiquement a l issu de la premiere requete ?
(desole j ai pas encore etudier les vues)

la table tblTemp est une table qui contient le résultat de la première requête.
les vues fonctionnent sur le principe de la table temporaire sauf qu'il y a gestion automatique (elle n'existe que quand on fait appelle à la vue, cela permet d'avoir une table toujours à jour, et de faire en même temps des économies d'espace de stockage )

Quoi qu il en soit encore merci de m aider Swoög, ca a du te demander un peu de temps.

De rien

Le principal est de bien voir ce que tu veux, ensuite, en général les champs et la requête apparaissent d'eux même ^^

il y a juste quelques points à se souvenir : préférer les jointures aus sous-requête quand c'est possible...

Essayer de faire au plus simple... ou au moins compliquer

décortiquer le problème en problèmes plus simple ^^

[Slumpy]

par pourcentages... tu veux dire que la somme est toujours égale à 1 ? il n'y a pas besoin de diviser par la somme des coeficients ?

dans ce cas, il te suffit de retirer la partie "/SUM(M.Coeficient)" de la requête

Oui, mais bon je preferre autant comme t as fait ^^

deux tables ont le même alias

Ces deux tables sont pourtant les memes non ?

la table tblTemp est une table qui contient le résultat de la première requête.
les vues fonctionnent sur le principe de la table temporaire sauf qu'il y a gestion automatique (elle n'existe que quand on fait appelle à la vue, cela permet d'avoir une table toujours à jour, et de faire en même temps des économies d'espace de stockage )

Donc je peux enchainer les deux requetes comme tu les as ecrite, j ai rien besoin de definir entre ?

Je veux me pencher un peu sur le Inner Join car je capte pas tout. Mais merci encore.

[Médiat]

Si j'ai bien compris la question (entre autres que les coefficients et poids font un total de 1) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT SAM.StudentID, SAM.ModuleID, SUM(SAM.Score * A.Weight) AS Moyenne
FROM tblStudentAssessmentModule SAM INNER JOIN tblAssessment A
                                          ON SAM.AssessmentID = A.AssessmentID
GROUP BY SAM.StudentID, SAM.ModuleID
UNION ALL
SELECT SAM.StudentID, NULL, SUM(SAM.Score * A.Weight * M.Coefficient) 
FROM tblStudentAssessmentModule SAM INNER JOIN tblAssessment A
                                          ON SAM.AssessmentID = A.AssessmentID
                                    INNER JOIN tblModule M
                                          ON SAM.ModuleID = M.ModuleID
GROUP BY SAM.StudentId

Cette requête devrait donner une ligne par module plus une ligne de moyenne globale (avec ModuleId = NULL), pour avoir la moyenne globale sur les lignes des moyennes par module, il suffit d'encapsuler l'union précédente (avec une modification mineure).