Sum of column depending on values

Solution 1:

The awkward design here (relations with no real indication of such other than the shared Code column) is going to lead to suboptimal queries like this

DECLARE @ContinentToReport varchar(32) = 'North America';

;WITH x AS 
(
  SELECT Code FROM dbo.TableName 
  WHERE Continent = @ContinentToReport 
    AND Country IS NULL
)
SELECT ID = ROW_NUMBER() OVER (ORDER BY x.Code), 
       x.Code, 
       Continent = @ContinentToReport, 
       t.Country, 
       SumAmount = SUM(t.amount)
  FROM dbo.TableName AS t
  INNER JOIN x ON t.Code = x.Code
  WHERE t.Country IS NOT NULL
  GROUP BY x.Code, t.Country
  ORDER BY x.Code;

Output (though I made a guess at what ID means and why it's different then the ID and the source, and I find the Continent column is kind of redundant since it will always be the same):

• Example db<>fiddle

Solution 2:

The simplest query which returns the correct result seems to be something like this

select row_number() over (order by Code) ID, 
       Code, 
       'North America' Continent, 
       Country, 
       sum(amount) SumAmount
from dbo.TableName
where Country is not null
group by Code, Country
order by Code;

dbFiddle