Creating SQL Server (T-SQL) view that flattens nulls

Question

I'm having a lot of difficulty trying to create a view that flattens the data without nulls. I've supplied the code that creates two basic tables and my view code so you can see what I've tried so far. Please note that the two tables do not have a matching primary or foreign key column, so the summary in the view is created by just joining on City. I can't use XML because my team of data analysts all have intermediate skills and won't be able to understand it. I considered using a recursive CTE, but I can't get it right. The result produces 6 lines but I want 3 lines. Thanks for any ideas about a better way to achieve this.

CREATE TABLE A (
    OrdID int,
    Cat varchar(255),
    Qty int,
    City varchar(255),
    Ctry varchar(255)
);
INSERT INTO A (OrdID, Cat, Qty, City, Ctry)
VALUES (1, 'TV', 5,'London',  'England');
INSERT INTO A (OrdID, Cat, Qty, City, Ctry)
VALUES (2, 'Laptop', 3,'London', 'England');
INSERT INTO A (OrdID, Cat, Qty, City, Ctry)
VALUES (3, 'Laptop', 4, 'Berlin', 'Germany');

CREATE TABLE Cust (
    CustID int,
    CustType varchar(255),
    City varchar(255),
    NumItems int,
);
INSERT INTO Cust (CustID, CustType, City, NumItems)
VALUES (1, 'New', 'London', 2);
INSERT INTO Cust (CustID, CustType, City, NumItems)
VALUES (2, 'Returning','London', 5);
INSERT INTO Cust (CustID, CustType, City, NumItems)
VALUES (3, 'Returning','Berlin', 2);
INSERT INTO Cust (CustID, CustType, City, NumItems)
VALUES (4, 'New','Berlin', 8);

alter view My_View
as

With CTE_FlattenNulls
as
(
Select 
S.Cat
, S.Qty
, S.City
, S.Ctry 
, case when C.CustType like 'New' then sum(C.NumItems) end as NewC
, case when C.CustType like 'Returning' then sum(C.NumItems) end as RetC
from A as S
left join Cust as C
on S.City = C.City
group by 
S.Cat
, S.Qty
, S.City
, S.Ctry
, C.CustType
)
select 
Cat
,Qty
,City
,Ctry
,NewC
,RetC
,SUM(IsNull(NewC, 0) + IsNull(RetC, 0)) as TotC
from CTE_FlattenNulls
group by
Cat
,Qty
,City
,Ctry
,NewC
,RetC

go

Just adding the output that I wanted:

Cat	Qty	City	Cntry	NewC	RetCust	TotC
Laptop	4	Berlin	Germany	8	2	10
Laptop	3	London	England	2	5	7
TV	5	London	England	2	5	7

Answer 1

You were very close. See comments in code for explanation.

With CTE_FlattenNulls
as
(
Select S.Cat, S.Qty, S.City, S.Ctry,
    -- To do conditional summation case expression needs to be inside the SUM function
    sum( case when C.CustType like 'New' then C.NumItems else 0 end ) as NewC,
    sum( case when C.CustType like 'Returning' then C.NumItems else 0 end ) as RetC
from A as S
left join Cust as C
on S.City = C.City
group by 
S.Cat
, S.Qty
, S.City
, S.Ctry
-- then you do not need to group by this column and therefore you do not get extra rows
--, C.CustType 
)
select Cat, Qty, City, Ctry, NewC, RetC,
    -- As per your example, you would no longer need GROUP BY,
    --   therefore SUM function should be removed
    SUM(IsNull(NewC, 0) + IsNull(RetC, 0)) as TotC
from CTE_FlattenNulls
-- As per your example, you would no longer need GROUP BY
group by Cat, Qty, City, Ctry
-- ,NewC -- Definitely not needed anymore
-- ,RetC -- Definitely not needed anymore

Everything else stays the same