How to calculate mean and show the highest and lowest values? [closed]

Navigation
0

I started to study SQL SERVER and faced with the following problem:

  

create a function that calculates the average of the students and if it is GREATER THAN 7 the student should be considered approved; if not, disapproved.

How do I do this?

    
asked by anonymous 22.04.2018 / 17:56

1 answer

0

Edit

With the table passed by @LuzineteMaciel

obs: as commented by the Jose Diz has been added check if the values are null to avoid return with NULL 

SELECT
    dessecod                                AS dessecod,
    MatCod                                  AS MATERIA, 
    (ISNULL(BolNota1, 0) + ISNULL(BolNota2, 0))                 AS SOMA_NOTA,                     
    ((ISNULL(BolNota1, 0) + ISNULL(BolNota2, 0))/2)   AS MEDIA,
    CASE
        WHEN ((ISNULL(BolNota1, 0) + ISNULL(BolNota2, 0))/2) < 7 THEN 'Reprovado'
        else 'Aprovado'
    END                     AS RESULTADO
FROM TbBoletim
GROUP BY
    dessecod,
    MatCod,
    BolNota1,
    BolNota2

In the question, the description of the table is not informed, I have then created a structure and a query to illustrate a possible way to solve the problem. 

CREATE TABLE ALUNOS(
    ID INTEGER IDENTITY,
    NOME VARCHAR(150),
    MATERIA VARCHAR(20),
    NOTA DECIMAL(10,2)
)

INSERT INTO ALUNOS(NOME, MATERIA, NOTA) VALUES
    ('Leonardo', 'Matematica', 8),
    ('Leonardo', 'Matematica', 9),
    ('Leonardo', 'Matematica', 8),
    ('João', 'Matematica', 8),
    ('João', 'Matematica', 8),
    ('João', 'Matematica', 6),
    ('Maria', 'Matematica', 4),
    ('Maria', 'Matematica', 4),
    ('Maria', 'Matematica', 4)


SELECT
    NOME                    AS NOME_ALUNO,
    MATERIA                 AS MATERIA,
    SUM(NOTA)               AS SOMA_NOTA,
    COUNT(ID)               AS TOTAL_NOTA,
    (SUM(NOTA)/COUNT(ID))   AS MEDIA,
    CASE
        WHEN (SUM(NOTA)/COUNT(ID)) <7 THEN 'Reprovado'
        else 'Aprovado'
    END                     AS RESULTADO
FROM ALUNOS
GROUP BY
    NOME,
    MATERIA

Output: 

NOME_ALUNO  MATERIA     SOMA_NOTA   TOTAL_NOTA  MEDIA       RESULTADO
João        Matematica  22.00       3           7.333333    Aprovado
Leonardo    Matematica  25.00       3           8.333333    Aprovado
Maria       Matematica  12.00       3           4.000000    Reprovado
    
22.04.2018 / 18:53
Click on the title bar and do not sort the column in dataTables Foreach array and create a name for array with same ID