CODEWITHSUNDEEP
sqlsql-server-2012
Chris
Chris

Reputation: 343

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;

Upvotes: 1

Views: 298

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

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.

Upvotes: 1

Related Questions