CODEWITHSUNDEEP
sqlsql-server
Maduro
Maduro

Reputation: 725

SQL Server: Sum values from multiple rows into one row

I am trying to sum the values from multiple rows. For example:

My output is: 

ID  Value
---------
1    3
1    4

What I would like to see is:

ID  Value
---------
1   7

This is my code:

SELECT 
   id CASE sum(cast(value as float)) 
   WHEN 0 THEN [other_value] 
   else ISNULL([value] ,'') 
   end  AS 'Value'
FROM table1
WHERE id = 1
GROUP BY id

I saw some solutions online such as I had to include the GROUP BY in order to avoid this error:

is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

But, still no sum.

NOTE: Values are varchar, therefore, I need the cast

Upvotes: 1

Views: 2804

Answers (3)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You just need to group by the id column.

select id, sum(cast(value as numeric))
from tablename
group by id

Edit:

SELECT 
id,
sum (
case when cast(value as numeric) = 0 THEN cast([other_value] as numeric) 
else cast(ISNULL([value] ,0) as numeric)
   ) end 
AS totalvalue
FROM table1
GROUP BY id

Upvotes: 4

Brian Pressler
Brian Pressler

Reputation: 6713

If what you are trying to do is get is the sum per ID of [value] if non-zero otherwise [other_value]... this is what you need:

select
    id,
    SUM(coalesce(nullif(cast([value] as float),0), cast([other_value] as float), 0)) [Value]
from table1
group by id

Upvotes: 1

VenoMpie
VenoMpie

Reputation: 45

You are trying to sum a string (even if you cast it) The query will only work if your ISNULL goes to a 0 or some numeric value

SELECT
id, SUM(CASE WHEN CAST(value AS FLOAT) = 0
THEN CAST([other_value] AS FLOAT)
ELSE ISNULL([value], 0) 
END) AS 'Value'
FROM table1
WHERE id = 1
GROUP BY id

Upvotes: 2

Related Questions