By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table email_archive (CompanyID varchar(100), Attachment varchar(100))
insert into email_archive values ('HG1', 'roses.txt')
1 rows affected
insert into email_archive values ('HG3', 'roses.txt')
1 rows affected
insert into email_archive values ('HG4', 'roses.txt')
1 rows affected
insert into email_archive values ('HG5', null)
1 rows affected
insert into email_archive values ('HG1', 'roses.txt')
1 rows affected
insert into email_archive values ('HG1', 'roses.txt')
1 rows affected
insert into email_archive values ('HG3', null)
1 rows affected
insert into email_archive values ('HG4', 'roses.txt')
1 rows affected
insert into email_archive values ('HG5', null)
1 rows affected
insert into email_archive values ('HG1', 'roses.txt')
1 rows affected
select ea1.CompanyID,
case when max(ea3.cnt) is not null then
CONCAT(round((max(cast(ea3.cnt as float))/count(*) *100), 2), '%')
else
'0%'
end prc
from email_archive ea1
left join (select count(*) cnt
, ea2.CompanyID
from email_archive ea2
where ea2.attachment is null
group by ea2.CompanyID ) ea3
on ea1.CompanyID = ea3.CompanyID
group by ea1.CompanyID
| CompanyID | prc |
|---|---|
| HG1 | 0% |
| HG3 | 50% |
| HG4 | 0% |
| HG5 | 100% |
Warning: Null value is eliminated by an aggregate or other SET operation.