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.
| Author |
Topic |
|
fengfeng
Yak Posting Veteran
64 Posts |
 Posted - 2010-03-01 : 14:18:03
|
I have the following sql: Select devicelist count (*) as 'a_d'from historywhere state in ('In Process', 'fulfilled Order')and mail = '20100210'and code IN ('102', '103')group by devicelist This would return in 1 column the name of the devicelist, and then the count of it in the 2nd column,Sometimes the devidelist name would be abc def ghi, and sometimes its just abc. So it could be one word, or 2 or 3 or however many separated by the spaces. But the sql only counts it as 1. Is there a way to count the number of words and then multiply it by the count>? |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-03-01 : 14:31:23
|
| You could do thisselect 1 + len(devicelist) - len(replace(devicelist,' ','')) to get the number of words in devicelist.JimEveryday I learn something that somebody else already knew |
 |
|
|
fengfeng
Yak Posting Veteran
64 Posts |
Posted - 2010-03-01 : 14:39:34
|
quote:
Originally posted by jimf
You could do thisselect 1 + len(devicelist) - len(replace(devicelist,' ','')) to get the number of words in devicelist.JimEveryday I learn something that somebody else already knew
So if i wanted to multiple by the count i do:select (1 + len(devicelist) - len(replace(devicelist,' ',''))) * count (*)?also how can I layer in another select to say if devicelist is blank, then dont count, only count those with populated devicelist? And instead of showing every devicelist name, how can I just show the sum of everything excluding those without a devicelist description? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
|
|
|
|
|
Counting number of words