create table test(indkey nvarchar(2),datakey nvarchar(4),landline nvarchar(11),mobile nvarchar(11),email nvarchar(20))insert into testvalues ('1','0001','01234567890','0712345679','1@test.co.uk')insert into testvalues ('2','0001','01234567890','','1@test.co.uk')insert into testvalues ('3','0002','01234567891','','2@test.co.uk')insert into testvalues ('4','0002','01234567890','','2@test.co.uk')insert into testvalues ('5','0002','','07123456789','')insert into testvalues ('6','0003','01234567892','07123456791','')insert into testvalues ('7','0004','01234567893','07123456792','')insert into testvalues ('8','0005','01234567894','07123456793','2@test.co.uk')insert into testvalues ('9','0008','01234567895','07123456793','9@test.co.uk')
SELECT COUNT(*) AS Total, COUNT(DISTINCT datakey) AS Uniquedatakey, COUNT(DISTINCT CASE WHEN landline<>'' THEN landline END) AS Uniquelandline, COUNT(DISTINCT CASE WHEN mobile<>'' THEN mobile END) AS Uniquemobile, COUNT(DISTINCT CASE WHEN email<>'' THEN email END) AS Uniqueemail FROM test