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
| SELECT A1,BA1,X-Y1+Z,X-Y2+Z,X-Y3+Z,X-Y4+Z,X-Y5+Z,X-Y6+Z,X-Y7+Z from
(Select max(com) AS A1,max(code_com) AS A2 FROM Sc_Volontariste WHERE code_DEP = 22 group by code_com order by A2 asc) A
LEFT OUTER JOIN
(SELECT * from
(SELECT max(code_com) AS BA1 ,sum(conso_chauf_EF_init*cout) as X FROM Sc_Volontariste INNER JOIN coutenergie on Sc_Volontariste.cmbl_init = coutenergie.cmbl
WHERE CATL = 'Résidence Principale'
AND TYPL = 'Appartement'
AND HLML = 'Non-HLM'
AND STOC = 'Logé gratuitement'
AND TYPO = 'Pavillons post 68 (2000-05)'
AND code_DEP = 22
group by code_com order by BA1 asc) BA,
(SELECT max(code_com) AS BB1,sum(conso_Murs_EF*cout) as Y1,sum(conso_Toit_EF*cout) as Y2,sum(conso_Plan_EF*cout) as Y3,sum(conso_Fen_EF*cout) as Y4,sum(conso_Vent_EF*cout) as Y5,sum(conso_Syst_EF*cout) as Y6,sum(conso_chauf_EF*cout) as Y7 FROM Sc_Volontariste INNER JOIN coutenergie on Sc_Volontariste.cmbl = coutenergie.cmbl
WHERE CATL = 'Résidence Principale'
AND TYPL = 'Appartement'
AND HLML = 'Non-HLM'
AND STOC = 'Logé gratuitement'
AND TYPO = 'Pavillons post 68 (2000-05)'
AND code_DEP = 22
group by code_com order by BB1 asc) BB,
(SELECT max(code_com) AS BC1,sum(prod_elec_syst_ef* (select cout from coutenergie where CMBL ='Coût de revente électricité')) as Z FROM Sc_Volontariste
WHERE CATL = 'Résidence Principale'
AND TYPL = 'Appartement'
AND HLML = 'Non-HLM'
AND STOC = 'Logé gratuitement'
AND TYPO = 'Pavillons post 68 (2000-05)'
AND code_DEP = 22
group by code_com order by BC1 asc) AC
where BA1=BB1 AND BA1=BC1) B
on A2 = BA1 |
Partager