SQL Calcul de Moyenne [Résolu]

[devman2011]

Salut ,
J'ai les tables suivantes :

Classe ( clasID,libClas,nbre_elv)
Eleve (elvID,nom,prénom,datenais,#classeID,...)
Matière ( matID,libMat,typeMat)
Controle ( contrID,dateContr,TypeContr,#matID )
Note ( contrID,elvID,Note )

Je veux calculer la moyenne de chaque élève dans chaque matière et dans chaque trimestre mais la formule est assez complexe ce qui m'a posé beaucoup de frustrations pour la calculer en SQL , en voici la formule :

MoyMatière = [(CC + Devoir1 + Devoir 2)/3)*2 + Composition] / 5

CC = Contrôle Continu ; Devoir1 , Devoir2 et enfin un examen final qu'on appelle une composition.Tous ces contrôles , l'élève les passe chaque trimestre.
Voilà j'ai trouvé une solution qui consiste à diviser le problème en deux :

- calculer la première partie de la formule à savoir : [(CC + Devoir1 + Devoir2)/3] * 2 en faisant ça :

Code :

1
2
3
4
SELECT Eleve.elvID , nom , prénom , libMat , AVG(Note) * 2 AS Moy1
FROM Eleve e , Matière m , Controle c , Note n 
WHERE e.elvID=n.elvID AND m.matID=c.matID AND c.contrID=n.contrID AND c.dateContr >=#10/10/2011# AND c.dateContr <= #21/12/2011#
GROUP BY e.elvID , nom , prénom

Remarque sur la dateContr : Cette date me permet de référencer les différents types de contrôles à savoir CC , Devoir1 , Devoir2 et Composition , car le champ "Note" les englobe tous ( CC , Devoir1 , Devoir2 , Composition ).
C'est pour ça que j'ai mis "dateContr" compris entre deux dates qui correspondent en fait au 1er trimestre ----> qui va du 10/10/2011 au 21/12/2011, mais le problème comment faire référence à la composition dans la requête SQL , en d'autres termes : comment ajouter l'autre partie de la formule dans la requête SQL pour calculer la moyenne par élève et par matière.
Aidez moi s'il vous plait c'est le seul problème qui me reste à résoudre.

[CinePhil]

Dans l'autre discussion sur la structure de tes tables, tu avais eu la bonne idée de mettre dans la table controle l'identifiant du trimestre. En effet, je pense que les trimestres ne se déroulent pas chaque année aux mêmes dates et il me semble utile d'avoir une table des trimestres.

Trimestre (trimID, libTrim, dateDebutTrim, dateFinTrim)
Controle ( contrID,#MatiereID,TypeControle,DateControle ,#trimID)

On va commencer par calculer (CC + Devoir1 + Devoir 2)/3), c'est à dire la moyenne des notes qui ne sont pas du type 'composition' :

Code :

1
2
3
4
5
6
SELECT n1.elvID, c1.matID, c1.trimID, 
    AVG(n1.Note) AS moyenne_cc_dev1_dev2
FROM Note n1
INNER JOIN Controle c1 ON c1.contrID = n1.contrID
WHERE c1.TypeControle <> 'Composition'
GROUP BY n1.elvID, c1.matID, c1.trimID

Nota : Remarque au passage l'utilisation de la syntaxe normalisée depuis 1992 pour les jointures.

Ensuite on fait la même chose pour les compositions, mais cette fois pas besoin de GROUP BY pusiqu'il n'y a semble t-il qu'une seule composition par triplet {élève, matière, trimestre} :

Code :

1
2
3
4
5
SELECT n2.elvID, c2.matID, c2.trimID, 
   n2.Note) AS note_composition
FROM Note n2
INNER JOIN Controle c2 ON c2.contrID = n2.contrID
WHERE c2.TypeControle = 'Composition'

On joint les deux requêtes ainsi que les tables qui permettent de récupérer les informations en clair plutôt que les identifiants et on calcule la formule complète :

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
SELECT e.elvID, e.nom, e.prenom,
    m.libMat, t.libTrim, 
    ((t1.moyenne_cc_dev1_dev2 * 2) + t2.note_composition) / 5 AS Moyenne
FROM 
(
    SELECT n1.elvID, c1.matID, c1.trimID, 
        AVG(n.Note) AS moyenne_cc_dev1_dev2
    FROM Note n1
    INNER JOIN Controle c1 ON c1.contrID = n1.contrID
    WHERE c1.TypeControle <> 'Composition'
    GROUP BY n1.elvID, c1.matID, c1.trimID
) t1 
INNER JOIN 
(
    SELECT n2.elvID, c2.matID, c2.trimID, 
        n2.Note AS note_composition
    FROM Note n2
    INNER JOIN Controle c2 ON c2.contrID = n2.contrID
    WHERE c2.TypeControle = 'Composition'
) t2 
    ON t2.elvID = t1.elvID
    AND t2.matID = t1.matId
    AND t2.trimID = t1.trimID
INNER JOIN Eleve e ON e.elvID = t1.elvID
INNER JOIN Matiere m ON m.matID = t1.matID
INNER JOIN Trimestre t ON t.trimId = t1.trimID
ORDER BY e.nom, e.prenom, m.libMat, t.libTrim

[devman2011]

Salut ,

C'est avec une grande joie et une profonde reconnaissance que je vous remercie de tout mon coeur , vous m'avez ouvert les yeux sur une nouvelle approche pour l'analyse et la résolution de problèmes relatifs aux bases de données surtout le SQL qui est vraiment un puissant outil de traitement. Mille Mercis.