I have this table Student :
- Id (int , primary key identity)
- Name (varchar(20))
- Score (int)
- 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 ?
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;
You can try like this
select Id,Name ,Score ,RecordDate, sum(score) over(partition by name) as All_Student_Score from Student S
The below solution works for your requirement.
select Id,
Name,
Score,
RecordDate,
sum(score) over( partition by name ) as All_Student_Score
from Student;
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;
