SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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);

select * from A
select * from Cust


OrdID
Cat
Qty
City
Ctry


1
TV
5
London
England

2
Laptop
3
London
England

3
Laptop
4
Berlin
Germany




CustID
CustType
City
NumItems


1
New
London
2

2
Returning
London
5

3
Returning
Berlin
2

4
New
Berlin
8


select A.Cat,
       A.Qty,
       A.City,
       min(A.Ctry) as Country,
       sum(case when C.CustType = 'New' then C.NumItems else 0 end) as NewC,
       sum(case when C.CustType = 'Returning' then C.NumItems else 0 end) as RetCust,
       sum(C.NumItems) as TotC
from   A
  join Cust C on A.City = C.City
group by A.Cat,
         A.Qty,
         A.City
order by A.Cat, A.City


Cat
Qty
City
Country
NewC
RetCust
TotC


Laptop
4
Berlin
Germany
8
2
10

Laptop
3
London
England
2
5
7

TV
5
London
England
2
5
7


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


Cat
Qty
City
Ctry
RetC
TotC


Laptop
3
London
England
null
2

Laptop
3
London
England
5
5

Laptop
4
Berlin
Germany
null
8

Laptop
4
Berlin
Germany
2
2

TV
5
London
England
null
2

TV
5
London
England
5
5


drop table A
drop table Cust
Cat	Qty	City	Country	NewC	RetCust	TotC
Laptop	4	Berlin	Germany	8	2	10
Laptop	3	London	England	2	5	7
TV	5	London	England	2	5	7