Group Postgresql query results

Question

I have the regular 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      |

to 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

Result that I wanted

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

Answer 1

You don’t have to use case. It’s easier to count the nonzero:

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. Like the count account any nonzero so it will only count the true.

Answer 2

I did it this way 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