SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288) - 12.0.6372.1 (X64) 
	Dec 12 2019 15:14:11 
	Copyright (c) Microsoft Corporation
	Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 17763: ) (Hypervisor)



create table source(id_a int,id_b int,City varchar(50),result varchar(10) );

insert into source values(101,101,'NY','Ready');
insert into source values(102,102,'TN','Sold');
insert into source values(103,null,'TN','Missing');
insert into source values(104,104,'NY','Ready');
insert into source values(null,105,'NY','Missing');
insert into source values(106,106,'TN','Ready');
insert into source values(107,107,'TN','Sold');
7 rows affected

select * from source


id_a
id_b
City
result


101
101
NY
Ready

102
102
TN
Sold

103
null
TN
Missing

104
104
NY
Ready

null
105
NY
Missing

106
106
TN
Ready

107
107
TN
Sold


select 'NY' as City,sum(case when city='NY' then 1 else 0 end) as CNT,
            sum(case when city='NY' and result='Ready' then 1 else 0 end) as No_Ready,
            sum(case when city='NY' and result='Sold' then 1 else 0 end) as No_sold,
            sum(case when city='NY' and result='Missing' then 1 else 0 end) as No_Missing
from source
union all
select 'TN' as City,sum(case when city='TN' then 1 else 0 end) as CNT,
            sum(case when city='TN' and result='Ready' then 1 else 0 end) as No_Ready,
            sum(case when city='TN' and result='Sold' then 1 else 0 end) as No_sold,
            sum(case when city='TN' and result='Missing' then 1 else 0 end) as No_Missing
from source


City
CNT
No_Ready
No_sold
No_Missing


NY
3
2
0
1

TN
4
1
2
1

id_a	id_b	City	result
101	101	NY	Ready
102	102	TN	Sold
103	null	TN	Missing
104	104	NY	Ready
null	105	NY	Missing
106	106	TN	Ready
107	107	TN	Sold