SQL Comma separated string total count

Question

Schema :

SubscriberId NewsletterIdCsv 
------------ --------------- 
1     48,51,94     
2     43,22
3     33,11
4     90,61 

I Need to get the count for each rows NewsletterIdCsv and then add all of them up to get a total count of all rows, for the basic row count I am doing the following:

SELECT newsletteridcsv, len(newsletteridcsv) - len(replace(newsletteridcsv, ',', '')) +1 IndividualCount
FROM DBTABLE

This Gives me the Result :

NewsletterIdCsv IndividualCount
------------ --------------- 
48,51,94     3
43,22        2
33,11        2
90,61        2

How do I get the total count (In this example 9)?

Note : This table has 5 Million records and I do not think using a temp table to Insert count and then finally going through the temp table rows to accumulate the count is an optimized way? Also I am totally against using cursors for efficiency issues !

What's the best way to get the total count ?

Answer 1

You can use SUM to add them together:

SELECT SUM(len(newsletteridcsv) - len(replace(newsletteridcsv, ',', '')) +1) 
    AS TotalCount
FROM DBTABLE

Since you're just asking for the total count, you don't even need to GROUP BY anything.

Answer 2

You've already done the tricky part (len minus commas), now just run a sum of that:

SELECT sum(len(newsletteridcsv) - len(replace(newsletteridcsv, ',', '')) +1) as TotalCount
FROM DBTABLE