Multiply two columns in a select With Groupby

Question

How Can I Do This Multiplication? the table looks like this:

What has to be done is to group disciplines 11 and 13, count how many 'sit' F and Null have and multiply by 'qntd_aula'... I managed to reach that select...

select d.id, d.nome, (count(sit) * f.qntd_aula)F, ((count(sit is null) - count(sit))* f.qntd_aula) P, qntd_aula from frequencia_aluno_dia2020 f, disciplina d where idpessoa = 62644 and    d.id = f.iddisciplina group by d.id,qntd_aula;

but it is not grouped properly look..

How could I solve it? Leaving Lingua Portuguesa with F = 2 and P = 5.

Answer 1

With the WITH clause you can create a common expression at runtime, it is as if you create a view at the time of executing your query, which makes it much faster than a real view. For example in your case you could create a with, relegating all data from your table and creating a new column called sit_real turning all nulls into F. Thus:

WITH vw_dados (id, data, idescola, idturma, idmatricula, ra, idpessoa, sit, sit_real, sit_mat, iddisciplina, bio, email, app, sms, push, qntd_sala) AS
(
  SELECT id, data, idescola, idturma, idmatricula, ra, idpessoa, sit,
case when sit is null then 'F' else sit end as sit_real
, sit_mat, iddisciplina, bio, email, app, sms, push, qntd_sala
)
SELECT ...