MariaDB 10.3

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

select version();


version()


10.3.32-MariaDB


create table es_dashboard(Business_Function VARCHAR(40),	Host_Name VARCHAR(40), It_Services VARCHAR(40));
insert into es_dashboard values ('Commercial Banking', 'GigaTux', 'AAA');
insert into es_dashboard values ('Commercial Banking', 'GigaTux', 'CCC');
insert into es_dashboard values ('Wealth', 'HTX', 'RRR');
insert into es_dashboard values ('Wealth', 'HTX', 'DDD');
insert into es_dashboard values ('Commercial Banking', 'KDP', 'AAA');
insert into es_dashboard values ('Wealth', 'Fusion', 'FFF');
insert into es_dashboard values ('Commercial Banking', 'CreateX', 'QQQ');
insert into es_dashboard values ('Wealth', 'Icon', 'ZZZ');
select * from es_dashboard;


Business_Function
Host_Name
It_Services


Commercial Banking
GigaTux
AAA

Commercial Banking
GigaTux
CCC

Wealth
HTX
RRR

Wealth
HTX
DDD

Commercial Banking
KDP
AAA

Wealth
Fusion
FFF

Commercial Banking
CreateX
QQQ

Wealth
Icon
ZZZ


/* Actual Query */

SELECT 
    hst.Business_Function, 
    hst.HOST, 
    COALESCE (shr.SHARED_HOST,0) AS SHARED_HOST
FROM 
   (
    SELECT 
       Business_Function,
       COUNT(distinct Host_Name) as HOST
    FROM es_dashboard
    GROUP BY Business_Function
    ) AS hst

LEFT JOIN
   (
    SELECT Business_Function, 
          COUNT(DISTINCT HOST_Name) AS SHARED_HOST
    FROM
        (
         SELECT Business_Function, Host_Name, 
                COUNT(distinct It_services) AS it_cnt
         FROM es_dashboard AS esd
         GROUP BY Business_Function, Host_Name
        ) AS it
    WHERE it_cnt>1
    GROUP BY Business_Function
) AS shr
ON hst.Business_function = shr.business_function


Business_Function
HOST
SHARED_HOST


Commercial Banking
3
1

Wealth
3
1


   /* for ref: 1st sub-query: */
   
    SELECT 
       Business_Function,
       COUNT(distinct Host_Name) as HOST
    FROM es_dashboard
    GROUP BY Business_Function


Business_Function
HOST


Commercial Banking
3

Wealth
3


/* for ref: 2nd sub-query: */
   

SELECT Business_Function, 
          COUNT(DISTINCT HOST_Name) AS SHARED_HOST
    FROM
        (
         SELECT Business_Function, Host_Name, 
                COUNT(distinct It_services) AS it_cnt
         FROM es_dashboard AS esd
         GROUP BY Business_Function, Host_Name
        ) AS it
    WHERE it_cnt>1
    GROUP BY Business_Function


Business_Function
SHARED_HOST


Commercial Banking
1

Wealth
1

Business_Function	Host_Name	It_Services
Commercial Banking	GigaTux	AAA
Commercial Banking	GigaTux	CCC
Wealth	HTX	RRR
Wealth	HTX	DDD
Commercial Banking	KDP	AAA
Wealth	Fusion	FFF
Commercial Banking	CreateX	QQQ
Wealth	Icon	ZZZ