SEARCH FIRST 4 DIGITS THEN SUM

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

SEARCH FIRST 4 DIGITS THEN SUM

Author

Topic

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2014-05-18 : 23:43:09

CASE STatEMENT OR FUNCTION THAT searches a telephone number column and when it finds numbers first 4 digits that begin with a |888, |800 , |877, do a sum of all those numbers , else sum the other numbers not matching the criteria

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-19 : 11:05:56

Did you mean sum, or count?This calculates the count

SELECT
SUM(CASE WHEN TelephoneNumber LIKE '888%' or 
   TelephoneNumber like '800%' or TelephoneNumber like '877%' THEN 1 ELSE 0 END) AS TollFreeCount,
SUM(CASE WHEN TelephoneNumber NOT LIKE '888%' AND 
   TelephoneNumber NOT like '800%' ANDTelephoneNumber NOT like '877%' THEN 1 ELSE 0 END) AS OtherCount
FROM YourTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources