1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
|
WITH
MOY AS (SELECT N_INSCRIPTION,CODE_MODULE,CODE_SEMESTRE,(CONTROLE1+CONTROLE2+(EXAMEN*2))/4 AS MOYENNE
FROM RESULTATS R),
MS AS (
SELECT N_INSCRIPTION,CODE_SEMESTRE,COUNT(1) NB_MODULES,SUM(moyenne)/COUNT(1) AS MOYENNE_SEMESTRE
FROM MOY
WHERE CODE_SEMESTRE IN ('S1','S2')
GROUP BY N_INSCRIPTION,CODE_SEMESTRE),
MA AS (SELECT N_INSCRIPTION,COUNT(1) NB_SEMESTRES,SUM(MOYENNE_SEMESTRE) AS TOTAL_ANNEE FROM MS GROUP BY N_INSCRIPTION)
SELECT MS.N_INSCRIPTION,1,E.NOM,E.PRENOM,MS.CODE_SEMESTRE,MS.MOYENNE_SEMESTRE FROM MS JOIN ETUDIANTES E ON E.N_INSCRIPTION=MOY.N_INSCRIPTION
UNION
SELECT MA.N_INSCRIPTION,2,E.NOM,E.PRENOM,'ANNEE',MA.TOTAL_ANNEE/NB_SEMESTRES FROM MS JOIN ETUDIANTES E ON E.N_INSCRIPTION=MOY.N_INSCRIPTION |
Partager