CODEWITHSUNDEEP
sqlsql-serversum
softech
softech

Reputation: 376

select SUM of a column with fetching number of other columns

I have a table like

student_id  student_name    subject marks
1              A            a_sub   10
1              A            a_sub   10
2              A            b_sub   5
3              A            c_sub   20
4              B            b_sub   10

from where I want to fetch SUM(marks) with all other columns for ex, SELECT student_name, subject, SUM(marks) AS marks FROM records ORDER BY student_id.

Result should be like:

student_name    subject marks
A               a_sub   45
B               b_sub   10

I tried with GROUP BY but it will not work as a student_name has different subject value

Upvotes: 0

Views: 89

Answers (3)

Hammad Shabbir
Hammad Shabbir

Reputation: 741

You can get your output using this query 

select distinct b.student_name, b.marks, (select top 1 subject from [records] where b.student_name = student_name)
from [records] a
inner join (
        SELECT student_name, SUM(marks) AS marks 
        FROM [records] 
        GROUP BY student_name) b on a.student_name = b.student_name
ORDER BY b.student_name

I have also tried on my end its working fine

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use window function: 

select distinct r.student_name, 
       first_value(r.subject) over (partition by r.student_name order by r.student_id) as subject,
       sum(r.marks) oever (partition by r.student_name) as marks
from records r;

Simple aggregation would also work : 

select student_name, min(subject) as subject, sum(marks) as marks 
from records r 
group by student_name;

Upvotes: 0

user330315
user330315

Reputation:

If you just want the "first" subject, you can use min() and group by the name. 

SELECT student_name, min(subject) as subject, SUM(marks) AS marks 
FROM records 
GROUP BY student_name
ORDER BY student_name;

Upvotes: 1

Related Questions