Group SQL values by two fields

Question

I’m trying to group a table so I can make a Count, but I’m having difficulties.

The structure of the table is as follows:

ID - NOME - IDADE - ESCOLARIDADE
1  - João -   15  - Ensino Fundamental Completo
2  - José -   25  - Ensino Fundamental Completo
3  - Mara -   13  - Ensino Fundamental Completo
4  - Joca -   50  - Ensino Fundamental Incompleto

I need to group and count as follows, count how many people between the age of 12 and 65 have completed elementary school, how many have incomplete. The result of the query in the table shown would be:

0-12 - Ensino Fundamental Completo - TOTAL: 0
12-65 - Ensino Fundamental Completo - TOTAL: 3
0-12 - Ensino Fundamental Incompleto - TOTAL: 0
12-65 - Ensino Fundamental Incompleto - TOTAL: 1

I have tried several ways and nothing has worked. Can anyone help me? Ah, schooling is in another table, it has a foreign key too, but not putting it in select. I am using Postgresql

Answer 1

You can do it like this

SELECT CASE 
          WHEN IDADE <= 12 THEN '0-12' 
          WHEN IDADE BETWEEN 13 AND 65 THEN '13-65'
          WHEN IDADE > 65 THEN '66 ou mais'
       END AS FaixaEtaria,
       ESCOLARIDADE,
       COUNT(DISTINCT ID) TotalAlunos
FROM TBL_TABELA
GROUP BY CASE 
            WHEN IDADE <= 12 THEN '0-12' 
            WHEN IDADE BETWEEN 13 AND 65 THEN '13-65'
            WHEN IDADE > 65 THEN '66 ou mais'
         END, ESCOLARIDADE

Thus, if for one of the combinations FaixaEtaria vs Escolaridade no students, this combination will not be represented in the final result.

For all combinations to be represented you need an auxiliary table, with all categories, which then joins/alloys to the results.

Something like that, for example:

SELECT Categorias.FaixaEtaria,
       Categorias.Escolaridade,
       COALESCE(TotalAlunos, 0) Total,
FROM (
     SELECT '0-12' FaixaEtaria, 'Ensino Fundamental Completo' ESCOLARIDADE UNION ALL
     SELECT '0-12',  'Ensino Fundamental Incompleto' UNION ALL
     SELECT '13-65', 'Ensino Fundamental Completo'   UNION ALL
     SELECT '13-65', 'Ensino Fundamental Incompleto' UNION ALL
     SELECT '66 ou mais', 'Ensino Fundamental Completo'   UNION ALL
     SELECT '66 ou mais', 'Ensino Fundamental Incompleto' 
) Categorias
LEFT JOIN (
   SELECT CASE 
             WHEN IDADE <= 12 THEN '0-12' 
             WHEN IDADE BETWEEN 13 AND 65 THEN '13-65'
             WHEN IDADE > 65 THEN '66 ou mais'
          END AS FaixaEtaria,
          ESCOLARIDADE,
          COUNT(DISTINCT ID) TotalAlunos
   FROM TBL_TABELA
   GROUP BY CASE 
              WHEN IDADE <= 12 THEN '0-12' 
              WHEN IDADE BETWEEN 13 AND 65 THEN '13-65'
              WHEN IDADE > 65 THEN '66 ou mais'
            END, ESCOLARIDADE
) Totais
    ON Totais.FaixaEtaria = Categorias.FaixaEtaria
   AND Totais.ESCOLARIDADE = Categorias.ESCOLARIDADE
ORDER BY 1, 2

Stay here the Sqlfiddle