fies.sql

--
--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 fies_ag 
AS 
SELECT 
    fies.codigo_contrato_fies,
    fies.genero,
    fies.raca_cor,
    fies.nome_ies,
    fies.codigo_ies,
    fies.campus,
    fies.codigo_campus,
    fies.percentual_solicitado_financiamento,
    fies.tipo_fianca,
    fies.sigla_uf,
    estado.regiao_id,
    fies.curso, 
    ano_censo,
    COUNT(DISTINCT codigo_contrato_fies) AS total 
    
FROM 
    fies 
        INNER JOIN 
    estado 
        ON (fies.sigla_uf=estado.sigla) 
WHERE  
    (ano = ano_censo)
GROUP BY 
    fies.codigo_contrato_fies,
    fies.genero, 
    fies.curso, 
    fies.raca_cor, 
    fies.nome_ies, 
    fies.codigo_ies, 
    fies.campus, 
    fies.codigo_campus, 
    fies.percentual_solicitado_financiamento, 
    fies.tipo_fianca, 
    fies.sigla_uf, 
    estado.regiao_id, 
    fies.ano_censo, 
    ano_censo 
ORDER BY 
    fies.genero ASC,
    fies.curso ASC,  
    fies.raca_cor ASC, 
    fies.nome_ies ASC, 
    fies.codigo_ies ASC, 
    fies.campus ASC, 
    fies.codigo_campus ASC, 
    fies.percentual_solicitado_financiamento ASC, 
    fies.tipo_fianca ASC, 
    fies.sigla_uf ASC, 
    estado.regiao_id ASC, 
    fies.ano_censo ASC, 
    ano_censo ASC
;