MS SQL Server - stored procedure help

ultim · 10 Sep 2009 at 14:43

I have a basic select statement i.e.

SELECT
ID, A, B, C
FROM
tblScores

I would like to also return the sum of A,B,C AS 'Total'

However A,B or C can be nulls

I assume i would have to check for nulls and then them together? How do i achieve this?

Blackstar_solar · 10 Sep 2009 at 15:00

You can't return the SUM as well as the actual values in one query but to get the sum do
SELECT
ID, SUM(ISNULL(A, 0)) + SUM(ISNULL(B, 0)) + SUM(ISNULL(C, 0)) AS [Total]
FROM
tblScores
GROUP BY ID

GravyMonster · 10 Sep 2009 at 15:27

You can return the lot with this subquery.

Code:

SELECT s.ID, s.A, s.B, s.C, sub.Total
FROM tblScores s
INNER JOIN 
	(SELECT s.ID, SUM(ISNULL(s.A, 0)) + SUM(ISNULL(s.B, 0)) + SUM(ISNULL(s.C, 0)) AS [Total]
	FROM tblScores s GROUP BY s.ID) sub
ON s.ID = sub.ID

ultim · 10 Sep 2009 at 15:28

Thanks! Appreciate the help.

Had to take out the Sum keyword to get it to work.

SELECT
ID, (ISNULL(A, 0)) + (ISNULL(B, 0)) + (ISNULL(C, 0)) AS [Total]
FROM
tblScores

AJK · 10 Sep 2009 at 15:29

SUM is used to get the sum of values from all rows - sounds like that isn't what you want.

As far as I can see, you just need this, it's pretty straightforward:

Code:

select
	id,
	a,
	b,
	c,
	isnull(a,0)+isnull(b,0)+isnull(c,0) as total
from
	TABLE

SimonCHere · 10 Sep 2009 at 15:37

Blackstar_solar's code is missing a group by clause which is the reason it won't work.

Blackstar_solar · 10 Sep 2009 at 16:00

dam, you are correct. Post edited above.