Geek Answers Handbook

SQL

Scheme:

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

I need to get a counter for each line of NewsletterIdCsv, and then add all of them to get the total number of all lines, for the base number of lines, I do 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 to get the total (in this example 9)?

Note. There are 5 million records written in this table, and I don’t think that using a temporary table to insert a counter, and then finally going through the rows of the temp table to accumulate the account is an optimized way? I am also strongly against using cursors to increase efficiency!

What is the best way to get a total score?

+4
source share
2 answers

You can use SUM to add them together:

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

Since you are simply requesting a total bill, you do not even need GROUP BY anything.

+6
source

You have already completed the hard part (len minus commas), now just run its sum:

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

More articles:


All Articles