How to do sum of column in mssql

Question

I have written the following query to get the name and amount :

  select fm.familyname,qt.amount
  from Registrations rs left join Family fm on fm.id = rs.family_id
  left join Quote qt on qt.id = rs.quote_id
  group by  fm.familyname,qt.amount

So from the above query i am getting the below answer:

name    amount
abc     1200
abc     1300
abc     1400

But i want the output like:

name    amount
 abc    3900

How can i do this? i have used Sum(isnull(cast(qt.amount as float),0)) as total but it is doing total of individual column.

How can i get the total ?

Answer 1

select fm.familyname,
SUM(
    CASE ISNUMeric(LTRIM(RTRIM(ISNULL(qt.amount,'0')))) WHEN 1 THEN CAST(qt.amount as decimal(10,2)) ELSE 0 END
) AS amount
from Registrations rs left join Family fm on fm.id = rs.family_id
left join Quote qt on qt.id = rs.quote_id
group by  fm.familyname

Answer 2

Simply GROUP BY fm.familyname alone:

select fm.familyname, SUM(qt.amount)
from Registrations rs
  left join Family fm on fm.id = rs.family_id
  left join Quote qt on qt.id = rs.quote_id
group by  fm.familyname 

If you get "Operand data type varchar(max) is invalid for sum operator.", you need to cast that column, something like:

select fm.familyname, sum(cast(qt.amount as float))
...