CREATE OR REPLACE VIEW EsordiReflusso AS
SELECT  E.Paziente, YEAR(E.DataEsordio)-YEAR(P.DataNascita) AS Eta, E.DataEsordio
FROM    esordio E INNER JOIN paziente P ON E.Paziente = P.CodFiscale
WHERE   E.Patologia = 'Reflusso gastroesofageo'
        AND YEAR(E.DataEsordio) = 2008;

CREATE OR REPLACE VIEW MeseCasi AS
SELECT  D.Mese, IF(MONTH(ER.DataEsordio) IS NULL, 0, COUNT(*)) AS NumCasi
FROM    EsordiReflusso ER
        RIGHT OUTER JOIN
        (
            SELECT  MONTH(E.DataEsordio) AS Mese
            FROM esordio E
            GROUP BY MONTH(E.DataEsordio)
        ) AS D ON MONTH(ER.DataEsordio) = D.Mese
GROUP BY D.Mese
ORDER BY D.Mese;

CREATE OR REPLACE VIEW MesiPiuCasi AS
SELECT  MC.Mese, MC.NumCasi
FROM    MeseCasi MC
WHERE   MC.NumCasi >= ALL (
                    SELECT  MC2.NumCasi
                    FROM    MeseCasi MC2
                    )
GROUP BY MC.Mese;

SELECT  IF(MPC.Mese IS NULL,MC.Mese, MPC.Mese),
        IF(MC.NumCasi = 0, NULL, MC.NumCasi),
        (
            SELECT  AVG(E2.Eta)
            FROM    EsordiReflusso E2
            WHERE   E2.DataEsordio <= E.DataEsordio

        ) AS EtaMediaPrec
FROM    MeseCasi MC
        NATURAL LEFT OUTER JOIN
        MesiPiuCasi MPC
        LEFT OUTER JOIN
        EsordiReflusso E ON MC.Mese = MONTH(E.DataEsordio)
WHERE   (MPC.Mese IS NULL AND MC.NumCasi = 0)
            OR (MC.NumCasi = MPC.NumCasi)
GROUP BY MC.Mese
ORDER BY MC.Mese;