How to calculate average and show the highest and lowest values?

Asked

Viewed 2,232 times

0

I started to study SQL SERVER and face 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, failed.

How do I do that?

sql-server
  • 1

    Welcome, avoid creating questions with uppercase titles and put what you have already tried to do (code)

  • A function or a procedure? Could you add the description of the table?

  • Sorry! my first time on forum

1 answer

0


Edit

With the table passed by @Luzinetemaciel

Obs: as commented by José Diz was added check if 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 is not informed the description of the table, so I 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

Exit:

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

  • sorry for the glitch: this would be the table I’m trying to create a function that calculates the average of the columns Bolnota1 ,Bolnota2 and if it is >= 7 the student should be considered approved if not failed IF OBJECT_ID('Tbboletim') IS NULL CREATE TABLE Tbboletim( Bolcod INTEGER NOT NULL IDENTITY(1,1), Bolnota1 DECIMAL(5,1), Bolnota2 DECIMAL(5,1), Bolfalta INTEGER, Matcod INTEGER, dessecod INTEGER );

  • @Luzinetemaciel add her to the question by using the [Edit button]

  • 1

    (1) Note: Bolnota1 and Bolnota2 cannot be part of the GROUP BY clause; (2) Tip: Since you are adding columns, check if the content exists (i.e., if there is no NULL) before adding. This avoids cases like (NULL + 8) = NULL

Browser other questions tagged sql-server

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