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
| SELECT imm.NUMERO_DOSSIER
, imm.LETTRE_DOSSIER
, COUNT(CASE log.NOMBRE_PIECE WHEN 1 THEN log.NOMBRE_PIECE END) AS 1p
, COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 1 THEN log.SURFACE_NETTE END), 2), 0) AS m2
, COUNT(CASE log.NOMBRE_PIECE WHEN 2 THEN log.NOMBRE_PIECE END) AS 2p
, COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 2 THEN log.SURFACE_NETTE END), 2), 0) AS m2
, COUNT(CASE log.NOMBRE_PIECE WHEN 3 THEN log.NOMBRE_PIECE END) AS 3p
, COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 3 THEN log.SURFACE_NETTE END), 2), 0) AS m2
, COUNT(CASE log.NOMBRE_PIECE WHEN 4 THEN log.NOMBRE_PIECE END) AS 4p
, COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 4 THEN log.SURFACE_NETTE END), 2), 0) AS m2
, COUNT(CASE log.NOMBRE_PIECE WHEN 5 THEN log.NOMBRE_PIECE END) AS 5p
, COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 5 THEN log.SURFACE_NETTE END), 2), 0) AS m2
, COUNT(CASE WHEN log.NOMBRE_PIECE BETWEEN 1 AND 5 THEN log.NOMBRE_PIECE END) AS Nb_log
, COUNT(*) AS Nb_log_2
, COALESCE(ROUND(SUM(CASE WHEN log.NOMBRE_PIECE BETWEEN 1 AND 5 THEN log.SURFACE_NETTE END), 2), 0) AS Surface
, COALESCE(ROUND(SUM(log.SURFACE_NETTE), 2), 0) AS Surface_2
FROM IMMEUBLE AS imm
JOIN LOGEMENT AS log
ON log.IMMEUBLE_ID_FK = imm.ID
WHERE log.NOMBRE_PIECE BETWEEN 1 AND 5
/*AND imm.TYPE_STATUT_IMMEUBLE_ID_FK >= 3*/
GROUP BY imm.NUMERO_DOSSIER
, imm.LETTRE_DOSSIER
ORDER BY imm.NUMERO_DOSSIER ASC
, imm.LETTRE_DOSSIER ASC; |
Partager