Join tables up together to get different counts of account type per postcode are I am trying to deterine the amount of account types per postcode range. I have the following code that works but I would like help in refactoring it as only 1 line of code changing between each 

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'New Connections'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 1
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)


select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Domestic Metered'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 2
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)

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Commercial Metered Low'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 3
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)

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Commerical Metered High'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 4
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)

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Domestic Keypad'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 5
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)

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Generator'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 6
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)


select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Commercial Keypad'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 7
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)


Any help appreciated.
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)