Group Query Results Postgresql

Question

Group Query Results Postgresql

Navigation

#1 by (3 votes)
#2 by (0 votes)

3

I have the normal table with the query .

status    |curso |matricula|
--------- |------|---------|
Completo  |Inglês|001      |          
Cursando  |Mat   |002      |          
Reprovado |Esp   |003      |         
Completo  |Inglês|004      |
Cursando  |Esp   |007      |
Aguardando|Esp   |008      |

a query :

SELECT  
    m.statusmat.descricao      AS status,
    m.cursos.nome              AS curso, 
    m.alunos.mat               AS matricula       
FROM p.pessoas
JOIN m.alunos     ON m.alunos.pessoa = p.pessoas.codigo
JOIN m.matriculas ON m.matriculas.aluno = m.alunos.codigo
AND m.matriculas.codigo =
  (SELECT codigo1
   FROM m.sp_matri125(m.alunos.codigo))
JOIN m.turmas              ON m.turmas.codigo = m.matriculas.turma
JOIN m.periodosletivos     ON m.periodosletivos.codigo = m.turmas.periodlet
JOIN m.cursos              ON m.cursos.codigo = m.turmas.curso
JOIN m.statusmat           ON m.statusmat.codigo = m.matriculas.status
LEFT JOIN pqs.linhaspesquisa ON pqs.linhaspesquisa.codigo = m.matriculas.linhapesquisa

The result I wanted

Completo |Cursando |Aguardando|Reprovado| 
---------|---------|----------|-------- |
2        |2        |1         |1        |

postgresql group-by

asked by anonymous 25.11.2014 / 00:04

2 answers

0

I did this using a case and a count , as in the example that Motta passed. Look:

select
     count(case m.statusmat.descricao when 'Completo' 
           then m.statusmat.descricao) as Completo

28.11.2014 / 00:29

WPF window does not exceed screen Java ScheduleExpression

score 3 · Accepted Answer

You do not need to use case . It's easier to count non-nulls:

select
    count(m.statusmat.descricao = 'Completo' or null) as "Completo",
    count(m.statusmat.descricao = 'Cursando' or null) as "Cursando",
    count(m.statusmat.descricao = 'Reprovado' or null) as "Reprovado",
    count(m.statusmat.descricao = 'Aguardando' or null) as "Aguardando"

true or null is true . false or null is null . As count counts any non-null then it will only count the true ones.