CODEWITHSUNDEEP
sqlaggregate-functions
Lamloumi Afif
Lamloumi Afif

Reputation: 9081

Sum function in sql query

I have this table Student :

  1. Id (int , primary key identity)
  2. Name (varchar(20))
  3. Score (int)
  4. RecordDate (DateTime)

I need to select all the columns of this table plus an extra column that represents the 'Sum' of 'Score' column depending of the Name of the student.

I tried this but it didn't work 

select S.Id,S.Name ,S.Score ,S.RecordDate, ( select Sum(Score) from Student where Name= S.Name) as All_Student_Score
 from Student S;

How can I change this query ?

Upvotes: 0

Views: 101

Answers (4)

Matt
Matt

Reputation: 14341

Because no one showed you that your own solution should work if you just alias your table in your sub query you can do the following:

select
     S.Id,S.Name
     ,S.Score
     ,S.RecordDate
     ,(select Sum(Score) from Student s2 where s2.Name= S.Name) as All_Student_Score
from
     Student S;

Upvotes: 1

Teja
Teja

Reputation: 13534

The below solution works for your requirement.

select Id, 
       Name,
       Score,
       RecordDate,
       sum(score) over( partition by name ) as All_Student_Score 
  from Student;

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can try like this

select Id,Name ,Score ,RecordDate, sum(score) over(partition by name) as All_Student_Score from Student S

Upvotes: 1

Lamak
Lamak

Reputation: 70638

You can use a `JOIN`:

    SELECT S.*,
           T.All_Student_Score
    FROM Student S
    INNER JOIN (SELECT Name, SUM(Score) All_Student_Score
                FROM Student) T
        ON S.Name = T.Name;

Upvotes: 1

Related Questions