Chris Chris - 6 months ago 13
SQL Question

Select count of distinct occurrences of services per Client ID into new column

I am trying to create a new column that has the count of distinct service codes per Client ID. My last column in the second query has all 0s.

SELECT SERVICE_CODE, COUNT(SERVICE_CODE) AS SERVICE_COUNTS
FROM #TAPMAR
GROUP BY SERVICE_CODE

SELECT T.*, COALESCE(D.SERVICE_COUNTS, 0)
FROM #TAPMAR T
LEFT JOIN(
SELECT SERVICE_CODE, COUNT(*) AS SERVICE_COUNTS
FROM #TAPMAR
GROUP BY SERVICE_CODE) D ON D.SERVICE_CODE = T.CASE_NUM;

Answer

The problem is probably the join . . . I don't see why service_code should match case_num.

I would suggest window functions in any case:

SELECT T.*,
       COUNT(*) OVER (PARTITION BY SERVICE_CODE) as NumOnService,
       COUNT(*) OVER (PARTITION BY CASE_NUM) as NumOnCase
FROM #TAPMAR T;

This adds the count fro both service and case. I'm not sure which one you really want.

Comments