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 39 40 41 42 43 44 45 46
| SELECT COUNT(*) AS NB_AUTRES, (SELECT COUNT(*)
FROM cr
LEFT OUTER JOIN statut_ref sr
ON sr.statut_ref_id = cr.cr_statut_ref_id
WHERE statut_ref.statut_niveau IN (10, 20)) AS NB_1020,
(CAST((SELECT COUNT(*)
FROM cr
LEFT OUTER JOIN statut_ref sr
ON sr.statut_ref_id = cr.cr_statut_ref_id
WHERE statut_ref.statut_niveau IN (10, 20)) AS FLOAT)) / COUNT(*)) * 100.0 AS POURCENT
FROM cr
LEFT OUTER JOIN statut_ref sr
ON sr.statut_ref_id = cr.cr_statut_ref_id
WHERE sr.statut_niveau NOT IN (25, 175, 176, 180, 190, 200)
-- Avec une vue :
CREATE VIEW V_1020
AS
SELECT COUNT(*) NB_1020
FROM cr
LEFT OUTER JOIN statut_ref sr
ON sr.statut_ref_id = cr.cr_statut_ref_id
WHERE statut_ref.statut_niveau IN (10, 20);
SELECT COUNT(*) AS NB_AUTRES, (SELECT NB_1020 FROM V_1020) AS NB_1020,
(CAST((SELECT NB_1020 FROM V_1020) AS FLOAT)) / COUNT(*)) * 100.0 AS POURCENT
FROM cr
LEFT OUTER JOIN statut_ref sr
ON sr.statut_ref_id = cr.cr_statut_ref_id
WHERE sr.statut_niveau NOT IN (25, 175, 176, 180, 190, 200);
-- avec une CTE :
WITH CTE_1020
AS
(SELECT COUNT(*) NB_1020
FROM cr
LEFT OUTER JOIN statut_ref sr
ON sr.statut_ref_id = cr.cr_statut_ref_id
WHERE statut_ref.statut_niveau IN (10, 20))
SELECT COUNT(*) AS NB_AUTRES, (SELECT NB_1020 FROM CTE_1020) AS NB_1020,
(CAST((SELECT NB_1020 FROM CTE_1020) AS FLOAT)) / COUNT(*)) * 100.0 AS POURCENT
FROM cr
LEFT OUTER JOIN statut_ref sr
ON sr.statut_ref_id = cr.cr_statut_ref_id
WHERE sr.statut_niveau NOT IN (25, 175, 176, 180, 190, 200); |
Partager