How to use sum with condition relative to another field

Asked

Viewed 1,876 times

2

I have a table of evaluations in the format

TABLE AVALIACAO
(id_avaliacao, id_pessoa, concluida, segunda_avaliacao)

and another table that records the notes

TABLE NOTA
(id_nota, id_avaliacao, id_questao, nota)

Quite abstractly, what happens is that a person can have a second assessment if they ask for it. In this case, the value of the second evaluation_evaluation field would be char 'S'.

My question: how do I make an sql that picks up the sum of NOTE.note, grouping by person (by id_pessoa) and that, if it is a second evaluation, take the sum only of the second evaluation, instead of the two.

For example.

AVALIAÇÂO

id_avaliacao id_pessoa concluida segunda_avaliacao
1            1         'S'       'N'
2            1         'S'       'S'

NOTE TABLE NOTE

id_nota id_avaliacao id_questao nota
1       1            1          1
2       1            2          1
3       1            3          1
4       2            1          2
5       2            2          2
6       2            3          2

As person 1 had a second evaluation, the sum of her grades should be 6 (not 9).

sql

  • Passing by again, I was curious to know if my query solved your problem, so I decided to test it. It actually solves as I first thought. Here is the test: http://sqlfiddle.com/#! 3/276805/1/0. Has your problem been solved? Let the people who tried to help you know.

2 answers

2

SELECT 
    A.id_pessoa, SUM (N.nota)
FROM
    NOTA N 
    JOIN AVALIAÇÂO A on A.id_avaliacao = N.id_avaliacao
WHERE
    (
        A.segunda_avaliacao = 'N'
        AND NOT EXISTS
            (
                SELECT
                    id_avaliacao 
                FROM
                    AVALIAÇÂO
                WHERE
                    id_pessoa = A.id_pessoa 
                    and segunda_avaliacao = 'S'
            )
    )
    OR A.segunda_avaliacao = 'S'
GROUP BY
    A.id_pessoa

0

Subqueries and CASE must solve:

select a.id_avaliacao, a.id_pessoa, a.concluida, a.segunda_avaliacao, 
    (case when a.segunda_avaliacao = 'S' then
        (select sum(nota) from nota where id_avaliacao = a.id_avaliacao and a.segunda_avaliacao = 'S')
     else (select sum(nota) from nota where id_avaliacao = a.id_avaliacao and a.segunda_avaliacao = 'N')
    end) as nota
from avaliacao a
where ...

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.