CREATE VIEW statistique AS 
SELECT
    COUNT(*) AS total,
    -- Nombre d'hommes et de femmes
    SUM(CASE WHEN sexe = 'M' THEN 1 ELSE 0 END) AS nombrehomme,
    SUM(CASE WHEN sexe = 'F' THEN 1 ELSE 0 END) AS nombrefemme,

    -- Enfants (moins de 12 ans)
    SUM(CASE WHEN TIMESTAMPDIFF(YEAR, date_naiss, CURDATE()) < 12 THEN 1 ELSE 0 END) AS nbEnfant,
    SUM(CASE WHEN sexe = 'M' AND TIMESTAMPDIFF(YEAR, date_naiss, CURDATE()) < 12 THEN 1 ELSE 0 END) AS nbEnfantHomme,
    SUM(CASE WHEN sexe = 'F' AND TIMESTAMPDIFF(YEAR, date_naiss, CURDATE()) < 12 THEN 1 ELSE 0 END) AS nbEnfantFemme,

    -- Adolescents (entre 12 et 23 ans)
    SUM(CASE WHEN TIMESTAMPDIFF(YEAR, date_naiss, CURDATE()) BETWEEN 12 AND 23 THEN 1 ELSE 0 END) AS nbAdolescents,
    SUM(CASE WHEN sexe = 'M' AND TIMESTAMPDIFF(YEAR, date_naiss, CURDATE()) BETWEEN 12 AND 23 THEN 1 ELSE 0 END) AS nbAdolescentsHomme,
    SUM(CASE WHEN sexe = 'F' AND TIMESTAMPDIFF(YEAR, date_naiss, CURDATE()) BETWEEN 12 AND 23 THEN 1 ELSE 0 END) AS nbAdolescentsFemme,

    -- Adultes (entre 23 et 65 ans)
    SUM(CASE WHEN TIMESTAMPDIFF(YEAR, date_naiss, CURDATE()) BETWEEN 23 AND 65 THEN 1 ELSE 0 END) AS nbAdulte,
    SUM(CASE WHEN sexe = 'M' AND TIMESTAMPDIFF(YEAR, date_naiss, CURDATE()) BETWEEN 23 AND 65 THEN 1 ELSE 0 END) AS nbAdulteHomme,
    SUM(CASE WHEN sexe = 'F' AND TIMESTAMPDIFF(YEAR, date_naiss, CURDATE()) BETWEEN 23 AND 65 THEN 1 ELSE 0 END) AS nbAdulteFemme,

    -- Vieux (plus de 65 ans)
    SUM(CASE WHEN TIMESTAMPDIFF(YEAR, date_naiss, CURDATE()) > 65 THEN 1 ELSE 0 END) AS nbVieux,
    SUM(CASE WHEN sexe = 'M' AND TIMESTAMPDIFF(YEAR, date_naiss, CURDATE()) > 65 THEN 1 ELSE 0 END) AS nbVieuxHomme,
    SUM(CASE WHEN sexe = 'F' AND TIMESTAMPDIFF(YEAR, date_naiss, CURDATE()) > 65 THEN 1 ELSE 0 END) AS nbVieuxFemme

FROM personnes;