1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
|
SELECT COM.[Type CAB], COM.A, COM.NbDeCompte,VOL.trafic
FROM
(SELECT [Type CAB], COUNT(A) AS NbDeCompte, A AS version
FROM (SELECT DISTINCT compte, [Type CAB], IIf([version routing]='4','conforme',IIf([version routing]='Version routage inconnu','Version routage inconnu','non conforme')) AS A
FROM table_import)
GROUP BY [Type CAB], IIf([version routing]='4','conforme',IIf([version routing]='Version routage inconnu','Version routage inconnu','non conforme')), A) AS COM
LEFT OUTER JOIN
(SELECT [Type CAB], IIf([version routing]='4','conforme',IIf([version routing]='Version routage inconnu','Version routage inconnu','non conforme')) AS A, sum(volume) AS trafic
FROM table_import
group by [Type CAB], IIf([version routing]='4','conforme',IIf([version routing]='Version routage inconnu','Version routage inconnu','non conforme'))) AS VOL
ON COM.[Type CAB]=VOL.[Type CAB] AND COM.A=VOL.A; |
Partager