Use of select with SUM() function

Asked

Viewed 18,706 times

2

I am developing a database for a small application.

My bank has 3 tables: students, disciplines and boletim_escolar, this last one I use to insert 3 individual notes.

I was able to calculate the average of these NOTES through the SUM() function until then everything, my difficulty is in displaying the three notes of the TABLE followed by this result of the SUM function().

How do I do it correctly? I am using SQL SERVER 2008 R2.

-- select from the banknote table 

SELECT * FROM boletim_escolar

-- function that calculate the AVERAGE OF EACH STUDENT - Moses Ramos 7 mins ago 

SELECT sum(((nota1*1)+(nota2*1)+(nota3*2))/4) AS 'Media Final' 
FROM boletim_escolar GROUP BY id_codaluno

Assuming the table is with the notes would like to display the result of the SUM() next to the nota3 column

Nota1 | nota2 | nota3 | Average ???

It will be possible to do this urgently needed such HELP.

sql-server select
  • 2

    get a little difficult understand what you are trying to do without having the table structure and your select. edit your question with this information.

  • Moses, edit your question and put your remarks to the question

  • Want to see the table script? HOW to upload

  • 3

    SELECT nota1, nota2, nota3, sum(((nota1*1)+(nota2*1)+(nota3*2))/4) AS 'Media Final' FROM

  • @Pedrocamarajunior, this is going to be a mistake.

  • This is the error : Msg 8120, Level 16, State 1, Line 1 Column 'school bulletin.Nota1' is invalid in the select list because it is not contained in either an Aggregate Function or the GROUP BY clause.

  • @We’re sorry, there’s no way you can display the note to each line that way. the fact that you use GROUP BY will group all a user’s lines this may exist many lines, but if it is the case to have the average for each line then it does not make sense you use the SUM() just sum and divided or use the AVG

  • @Marconcíliosouza is right, I did not pay much attention. I believe that if we add a SUM() each note will solve, as all values will be grouped. I had not committed myself to the GROUP BY in consultation. SELECT id_codaluno, SUM(nota1), SUM(nota2), SUM(nota3), "MEDIA" FROM

  • @We are a student can or will have more than one boletim_escolar?

  • Not at the first moment.

  • Note. I am calculating the weighted average where nota3 has weight 2.

  • I have inserted the following query : "SELECT sum(Nota11), sum(nota21), sum(note3*2) AS 'Student Media' FROM school bulletin GROUP BY id_codstudent". It returns only the result of the calculations. It would be possible to divide all these results by 4, whichever would be the sum of weighted averages?

Show 7 more comments

3 answers

2

    SELECT sum(nota1) AS 'Media Final' FROM boletim_escolar GROUP BY id_codaluno 

    Decimal N1='Soma da primira nota'*1

    SELECT sum(nota2) AS 'Media Final' FROM boletim_escolar GROUP BY id_codaluno 

    Decimal N2='Soma da segunda nota'*1


    SELECT sum(nota3) AS 'Media Final' FROM boletim_escolar GROUP BY id_codaluno 

    Decimal N3='Soma da 3ª  nota'*2

   decimal  media=(N1+N2+N3)/4

WHAT IS THE PROGRAMMING LANGUAGE??

  • SQL SERVER 2008

  • Language (e.g.: C#,java): only using Sql,

  • Together with the vb6.

  • This error occurs in the database: Msg 8120, Level 16, State 1, Line 1 Column 'school bulletin.Nota1' is invalid in the select list because it is not contained in either an Aggregate Function or the GROUP BY clause.

  • SELECT Nota1,nota2,nota3,(sum(Nota1)+sum(nota2)+sum(nota3*2))/4 AS 'Final Media' FROM boletim_escolar GROUP BY id_codaluno . This is the code I used

  • the aim is to provide a list of calculated averages ?

  • Yes eh this, if you can exemplify in a generic way

  • I just want to show off the three notes and the media

  • To do that, you’re using List<>?

Show 4 more comments

0

select nota1,nota2,nota3,sum(((nota1*1)+(nota2*1)+(nota3*2))/4) AS 'Media Final' 
from boletim_escolar 
GROUP BY id_codaluno

I didn’t understand why you use the sum() instead of the avg(), but then I noticed that the notes have weight.

  • And how could I do the right thing?

0

I don’t know why you use the SUM() function since you don’t have more than one line for each user, when in reality just use the + to sum the values.

declare @boletim_escolar table
(
    id_codaluno int,
    nota1 numeric(18,2),
    nota2 numeric(18,2),
    nota3 numeric(18,2)
)

insert into @boletim_escolar values
(1, 7.5 , 6.8 , 8.9),
(2, 1.5 , 7.8 , 3.9),
(3, 9.5 , 5.8 , 7.9)

SELECT nota1, (nota1*1) as 'nota1 Ponderada', nota2,  (nota2*1) AS 'nota2 Ponderada' ,
 nota3 ,(nota3*2) AS 'nota3 Ponderada',  
cast((((nota1*1)+(nota2*1)+(nota3*2))/4) as numeric(18,2)) AS 'Media Final Ponderada' 
FROM @boletim_escolar

See the resolution below.

nota1   nota1 Ponderada nota2   nota2 Ponderada nota3   nota3 Ponderada Media Final Ponderada
7.50    7.50    6.80    6.80    8.90    17.80   8.03
1.50    1.50    7.80    7.80    3.90    7.80    4.28
9.50    9.50    5.80    5.80    7.90    15.80   7.78

Browser other questions tagged sql-server select

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