Count in PostgreSQL using OVER

Question

Count in PostgreSQL using OVER

Navigation

#1 by (1 votes)

1

I have a teacher evaluation table and another one with student assessments:

tbl_professor

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

tbl_avaliacao

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

Student evaluation can be R-Bad, B-Good, but how could you create a listing like below using OVER because 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 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.

postgresql

asked by anonymous 16.10.2015 / 06:16

1 answer

Auto component alignment does not appear in Visual Studio Change tabs via Modal bootstrap

score 1 · Accepted Answer

It is not restricted to PostgreSQL, but I would do it this way:

Ratings 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 do not 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 should have a lower cost than the query that is in the question, especially for high amount of records, since it has no recursion. You may need an alias for the subquery, I do not remember, but I used tb_tmp .

To put everything together in a row you only need the two queries with union all without grouping by teacher.

I did not test.

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