This code below gives me the following table:
ID PROVIDER_CDE GRF_COUNT ACU_COUNT
100034 GRF 4 1
100054 GRF 3 1
a.id, a.PROVIDER_CDE, a.GRF_COUNT, E.ACU_COUNT
(SELECT id, PROVIDER_CDE, count(*) GRF_COUNT
WHERE Provider_cde = 'GRF'
GROUP BY id, PROVIDER_CDE))A
SELECT ID,PROVIDER_CDE, count(*) ACU_COUNT
WHERE Provider_cde = 'ACU'
GROUP ID, PROVIDER_CDE))E
ON ID = ID
ORDER BY ID ASC
But it only gives me the ID where there is a count.
I need all the ID even if there is no count for example if i wanted to add another Column CLM_COUNT (below) all ID should be shown, not only the once where these is a count.
ID PROVIDER_CDE GRF COUNT ACU_COUNT CLM_COUNT
100034 GRF 4 1 0
100054 GRF 3 1 0