--
--Copyright (C) 2019 Centro de Computacao Cientifica e Software Livre
--Departamento de Informatica - Universidade Federal do Parana - C3SL/UFPR
--
--This file is part of SMPPIR-Tables.
--
--SMPPIR-Tables is free software: you can redistribute it and/or modify
--it under the terms of the GNU General Public License as published by
--the Free Software Foundation, either version 3 of the License, or
--(at your option) any later version.
--
--SMPPIR-Tables is distributed in the hope that it will be useful,
--but WITHOUT ANY WARRANTY; without even the implied warranty of
--MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--GNU General Public License for more details.
--
--You should have received a copy of the GNU General Public License
--along with SMPPIR-Tables.  If not, see <https://www.gnu.org/licenses/>.

CREATE TABLE jvt_educacao_ag
AS
SELECT 
    ano_censo,
    cod_uf,
    sgl_uf,
    cor_raca,
    CASE when (idade > 45) THEN 45
    ELSE idade
    END AS idade,
    nivel_max_escola AS maior_nivel,
    concluiu_frequentou,
    in_escola,
    serie_frequenta,
    nivel_escola,
    COUNT(*) AS total 

FROM 
    pnad_cont 

GROUP BY 
    ano_censo,
    sgl_uf,
    cod_uf,
    cor_raca,
    idade,
    maior_nivel,
    concluiu_frequentou,
    serie_frequenta,
    nivel_escola,
    in_escola

ORDER BY 
    ano_censo ASC,
    sgl_uf ASC,
    cod_uf ASC,
    cor_raca ASC,
    idade ASC,
    maior_nivel ASC,
    concluiu_frequentou ASC,
    serie_frequenta ASC,
    nivel_escola ASC,
    in_escola  ASC
;