Join tables up together to get different counts of account type per postcode are
I will try something like this:

select substring(PostOfficeBox,1,4) as 'Postcode'
    , COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as 'New Connections'
    , COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as 'Domestic Metered'
    , COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) as 'Commercial Metered Low'
    , COUNT(CASE WHEN New_accounttype = 4 THEN New_AccountType ELSE NULL END) as 'Commerical Metered High'
    , COUNT(CASE WHEN New_accounttype = 5 THEN New_AccountType ELSE NULL END) as 'Domestic Keypad'
    , COUNT(CASE WHEN New_accounttype = 6 THEN New_AccountType ELSE NULL END) as 'Generator'
    , COUNT(CASE WHEN New_accounttype = 7 THEN New_AccountType ELSE NULL END) as 'Commercial Keypad'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) = 'BT'
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
--having count(New_MPRNNumber) &gt; 0 
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)