Count in Postgresql using OVER

Question

I have a teacher evaluation table and another with student evaluations:

tbl_teacher

id | nome
----------------------------
1  | José
2  | Maria
3  | Paulo

tbl_evaluation

id | idprofessor | avaliacao | data
-------------------------------------
1  | 3           | R         | 2015-01-01
2  | 2           | B         | 2015-01-01
3  | 3           | B         | 2015-01-01

The student evaluation may be R-Bad, B-Good, but how could you create a listing like the one below using OVER, as it does not allow you to put a WHERE for this type of filtering?

avaliacoes_ruins | avaliacoes_boas
----------------------------------
1                | 2

Or

professor |avaliacoes_ruins |avaliacoes_boas
---------------------------------------------
José      |0                |0
Maria     |0                |1
Paulo     |1                |1

I know that the last case would be possible to do SQL

SELECT
    nome,
    (SELECT (COUNT) FROM tbl_avaliacao WHERE idprofessor = tbl_professor.id AND avaliacao = 'R') AS avaliacoes_ruins,
    (SELECT (COUNT) FROM tbl_avaliacao WHERE idprofessor = tbl_professor.id AND avaliacao = 'B') AS avaliacoes_boas
FROM
    tbl_professor

But in the above case the cost of SQL will be high.

Answer 1

It is not restricted to Postgresql, but I would do so:

Appraisal count

professor |avaliacao |quantidade
--------------------------------
José      |B         |0
José      |R         |0
Maria     |B         |0
Maria     |R         |1
Paulo     |B         |1
Paulo     |R         |1

With Inner Join, group by and Count.

If you really want to join in one line per teacher, you can use a sum with UNION ALL, I don’t know if this query works in Postresql without adaptation:

SELECT nome, SUM(avaliacoes_ruins), SUM(avaliacoes_boas)
FROM (
    SELECT p.nome, COUNT(a.avaliação) AS avaliacoes_ruins, 0 AS avaliacoes_boas
    FROM tbl_professor p INNER JOIN tbl_avaliacao a ON a.idprofessor = p.id
    WHERE a. avaliação = 'R' GROUP BY p.nome, avaliacoes_boas
  UNION ALL
    SELECT p.nome, 0 AS avaliacoes_ruins, COUNT(a.avaliação) AS avaliacoes_boas
    FROM tbl_professor p INNER JOIN tbl_avaliacao a ON a.idprofessor = p.id
    WHERE a.avaliação = 'B' GROUP BY p.nome, avaliacoes_ruins
) tb_tmp
GROUP BY nome

It must have a lower cost than the query that is in the question, mainly for high amount of records, because it has no recursion. You may need an alias for the subquery, I don’t remember, but I used tb_tmp.

To put everything together in one line you only need the two queries with Union all without the teacher group.

I haven’t tested.

Editing: I forgot to say: there must be indexes properly defined, otherwise there will be no query that makes your bank efficient in terms of speed.