T-SQL: Efficient way to add up column values

Now I'm sure this has been asked and superbly been answered on here. However, I am unable to find the answer since it touches many keywords.
I basically want to replace a table of the form:

Type   amount   param   note
7      2        str1    NULL
42     12       str2    NULL
128    7        str3    samplenote
42     12       NULL    NULL
101    4        str4    NULL
42     12       NULL    NULL
7      1        str1    samplenote
128    2        str5    NULL

with a table like:

Type   amount   param   note
7      3        str1    combined
42     36       NULL    combined
128    9        NULL    combined
101    4        str4    combined

In words, I seek to sum up the amount parameter based on its type while declaring param = NULL for all "unclear" fields. (param should be NULL when the param values of combined Types have more than one different content; else, param should have the original content.)

With my python background, I tackled this task with a for loop approach, iterating through the types, adding a new row for every type with summed up amount and note = 'combined', to then delete the remaining rows (see below). There has to be a more efficient way with some JOIN statement I'm sure. But how would that look like?

FYI, this is the solution I am working on (not functioning yet!):

/*** dbo.sourcetable holds all possible Type values ***/
CREATE PROCEDURE [sumup]

AS
BEGIN

DECLARE @i int = (SELECT TOP (1) Type FROM [dbo].[sourcetable] ORDER BY Type)
DECLARE @MaxType int = (SELECT TOP (1) Type FROM [dbo].[sourcetable] ORDER BY Type DESC)

DECLARE @sum int

BEGIN TRY
    WHILE @i <= @MaxType
    BEGIN
        IF EXISTS (SELECT * FROM [dbo].[worktable] WHERE Type = @i)
        BEGIN
            SET @sum = (SELECT SUM(amount) FROM [dbo].[worktable] WHERE Type = @i)

            BEGIN
                WITH cte AS (SELECT * FROM [dbo].[worktable] WHERE Type = @i)
                INSERT INTO [dbo].[worktable]
                    ([Type]
                    ,[amount]
                    ,[param]
                    ,[note]
                SELECT
                     cte.Type
                    ,@sum
                    ,cte.param
                    ,'combined'
                FROM cte
            END

            DELETE FROM [dbo].[worktable] WHERE Type = @i AND ISNULL([note],'') <> 'combined'

        END
        SET @i = @i + 1
    END

END TRY
BEGIN CATCH 

    -- some errorlogging code

END CATCH

END

GO

Solution

This can be achieved with a single select statement.

If you require your combined flag to only apply to where more than one row has been combined, add another case expression checking the result of either a count(1) for rows combined or count(distinct param) for unique param values combined:

declare @t as table(type int, amount int, param varchar(15), note varchar(15));
insert into @t values (7,2,'str1',NULL),(42,12,'str2',NULL),(128,7,'str3','samplenote'),(42,12,NULL,NULL),(101,4,'str4',NULL),(42,12,NULL,NULL),(7,1,'str1','samplenote'),(128,2,'str5',NULL);

select type
      ,sum(amount) as amount
      ,case when count(distinct isnull(param,'')) = 1
            then max(param)
            else null
            end as param
      ,'combined' as note
from @t
group by type
order by type;

Output:

+------+--------+-------+----------+
| type | amount | param |   note   |
+------+--------+-------+----------+
|    7 |      3 | str1  | combined |
|   42 |     36 | NULL  | combined |
|  101 |      4 | str4  | combined |
|  128 |      9 | NULL  | combined |
+------+--------+-------+----------+