deepak deepak - 4 months ago 10
SQL Question

How to do sum of column in mssql

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

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))
...