How to use sum with condition related to another field

Question

How to use sum with condition related to another field

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

I have a table of ratings 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 evaluation if they ask for it. In this case, the value of the second_validation field would be char 'S' .

My question: how do I make an sql that takes the sum of NOTA.nota, grouping by person (by id_pessoa ) and that, if it is a second evaluation, take the summation 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

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

sql

asked by anonymous 09.09.2014 / 03:58

2 answers

Error writing to MongoDB using Parallelism in C # Change font color of a cell Table with ajax

score 2 · Answer 1

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

score 0 · Answer 2

Subqueries and CASE should resolve:

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 ...