SQL Server: SUM Multiple columns without using group by

advertisements

I have table like this.

Table 1 :

ID Name s1 s2 s3 id_sub
-----------------------
1  John 90 80 90 1
2  Nick 80 70 90 1
3  Mike 95 95 80 1
4  John 70 70 70 2

Table 2 :

id_sub sub sub_name
---------------
1      ph  physic
2      mt  math
3      cm  chemistry

Query:

SELECT t_score.name, (t_score.s1 + t_score.s2 + t_score.s3) as score
FROM t_score, t_sub
where t_score.id_sub = t_sub.id_sub AND t_score.id_sub = 1
GROUP BY name

I want it return like this

Name score
----------
John 260
Nick 240
Mike 210

But it returns

Column 't_score.s1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
Column 't_score.s2' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
Column 't_score.s3' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

How to fix it?

[EDIT] i made a mistake on calculate score.

Name score
----------
John 260
Nick 240
Mike 270

score is SUM of each row contains s1+s2+s3


Use SUM:

SELECT t_score.name, SUM(t_score.s1 + t_score.s2 + t_score.s3) as score
FROM t_score
INNER JOIN t_sub ON t_score.id_sub = t_sub.id_sub
WHERE t_score.id_sub = 1
GROUP BY name

Also, it is preferable to use explicit join syntax, as in the above query.