CODEWITHSUNDEEP
sqlsql-server
user9591909
user9591909

Reputation:

Use the sum() on a result set returned by a subquery

This query returns the two lowests grades {9,10}

select TOP 2 grades.grade 
from grades
where grades.studentno = 1 
order by grade ASC

And I want to sum the result of grades with the sum() function and set the result of the sum() function to the variable @sumLowerGrades

I have attempted with this query

set @sumLowerGrades = sum(select TOP 2 grades.grade from grades
        where grades.studentno = 1 order by grade ASC)

But it fails with the following message:

Incorrect syntax near the keyword 'select'.
Incorrect syntax near ')'.

Upvotes: 0

Views: 81

Answers (2)

Ziad Mehmood
Ziad Mehmood

Reputation: 1

Also can do as the following:

SET @sumLowerGrades = (SELECT sum(grade)
from (select TOP 2 grades.grade 
      from grades
      where grades.studentno = 1 
      order by grade ASC
     ) g);

A FROM clause requires table name/Names, so when you see

from (select TOP 2 grades.grade 
      from grades
      where grades.studentno = 1 
      order by grade ASC
     ) g

Is making this query/dataset as table and here g is the name given to the subquery within the brackets.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Use a subquery:

select @sumLowerGrades = sum(grade)
from (select TOP 2 grades.grade 
      from grades
      where grades.studentno = 1 
      order by grade ASC
     ) g;

Upvotes: 1

Related Questions