Group SQL values by two fields

Question

Group SQL values by two fields

Navigation

#1 by (1 votes)

0

I'm trying to group a table so I can count, but I'm having trouble.

The table structure 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 ages 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 many ways and nothing has worked. can anybody help me? Ah, schooling is on another table, it has a foreign key too, but not to putting on the select. I'm using PostgreSQL

sql postgresql

asked by anonymous 30.01.2016 / 15:55

1 answer

Display div according to screen resolution display listview in same activity after query

score 1 · Accepted Answer

You can do so

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

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

In order for all combinations to be represented, they need an auxiliary table, with all categories, which are then linked to the results.

Something like this, 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 the SQLFiddle