select occurrences - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

select occurrences

Author

Topic

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2009-04-28 : 05:39:18

is there any way to do a count of occurrences in a select

so if i had a varchar(column1) like below

row1'i love dogs, dogs are the best'
row2'i hate dogs'
row3'dogs, dogs'

and i did select * from table where column1 like '%dogs%' i could get the following results
row1 = 2
row2 = 1
row3 = 2

is it possible to do an accumulative count of the occurrences of a select value like above? any pointers appreciated

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-28 : 06:56:15

Try this

select col,(len(col)-len(replace(col,'dogs','')))/4 from your_table

Madhivanan

Failing to plan is Planning to fail

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2009-04-28 : 07:18:54

ok, that's perfect, next annoying question- say i have another column, col2(varchar again)

col2
row1 'dogs and monkeys'
row2 'dogs eat food'
row3 'dogs don't like dog food'

i presume i can do something like this

SELECT col, (LEN(col) - LEN(REPLACE(col, 'dogs', ''))) / 4 AS colCount1,(LEN(col2) - LEN(REPLACE(col2, 'dogs', ''))) / 4 AS colCount2
FROM tblTestSearch

but how would i add colCount1 and colCount2 together to create a totalCount as a fourth column?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-28 : 07:21:23

SELECT col,colCount1,colCount2,colCount1+colCount2 as total_colCount FROM
(
SELECT col, (LEN(col) - LEN(REPLACE(col, 'dogs', ''))) / 4 AS colCount1,(LEN(col2) - LEN(REPLACE(col2, 'dogs', ''))) / 4 AS colCount2
FROM tblTestSearch
) as t

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-28 : 07:21:23

Wow madhi!
Cool and simple.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2009-04-28 : 07:23:24

now you're just showing off :), cheers madhi, you are a true gentleman and a genius

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-28 : 07:23:35

quote:
Originally posted by webfred

Wow madhi!
Cool and simple.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-28 : 07:24:25

quote:
Originally posted by neil_akoga

now you're just showing off :), cheers madhi, you are a true gentleman and a genius

Thanks

Madhivanan

Failing to plan is Planning to fail

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2009-04-28 : 07:48:03

ok, I've found something wrong with it i think

here's the test query

SELECT col, (LEN(col) - LEN(REPLACE(col, 'dogs', ''))) / 4 AS col3, (LEN(col) - LEN(REPLACE(col, 'dogs', ''))) / 4 AS col4, (LEN(col) - LEN(REPLACE(col, 'dogs', '')))
/ 4 + (LEN(col) - LEN(REPLACE(col, 'dogs', ''))) / 4 AS colCount
FROM tblTestSearch

and here's the data it produces

i love dogs, dogs are the best 2 2 4
i hate dogs 1 1 2
dogs, dogs 2 2 4
cats 0 0 0
monkey dogs dogs dogs dogs dogs 6 6 12
dog 0 0 0
dogs 1 1 2

any idea why the row with 'monkey dogs dogs dogs dogs dogs' comes back with a count of 6?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-28 : 08:16:17

It will give you a wrong count as long as the number of occurences exceeds 4.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-28 : 08:16:39

try this,

SELECT 
	col, 
	(LEN(col) - LEN(REPLACE(REPLACE(col,' ','#'), 'dogs', ''))) / 4 AS col3, 
	(LEN(col) - LEN(REPLACE(REPLACE(col,' ','#'), 'dogs', ''))) / 4 AS col4,
	(LEN(col) - LEN(REPLACE(REPLACE(col,' ','#'), 'dogs', ''))) / 4+(LEN(col) - LEN(REPLACE(REPLACE(col,' ','#'), 'dogs', ''))) / 4

FROM 
	tblTestSearch

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-28 : 08:42:30

quote:
Originally posted by neil_akoga

ok, I've found something wrong with it i think

here's the test query

SELECT col, (LEN(col) - LEN(REPLACE(col, 'dogs', ''))) / 4 AS col3, (LEN(col) - LEN(REPLACE(col, 'dogs', ''))) / 4 AS col4, (LEN(col) - LEN(REPLACE(col, 'dogs', '')))
/ 4 + (LEN(col) - LEN(REPLACE(col, 'dogs', ''))) / 4 AS colCount
FROM tblTestSearch

and here's the data it produces

i love dogs, dogs are the best 2 2 4
i hate dogs 1 1 2
dogs, dogs 2 2 4
cats 0 0 0
monkey dogs dogs dogs dogs dogs 6 6 12
dog 0 0 0
dogs 1 1 2

any idea why the row with 'monkey dogs dogs dogs dogs dogs' comes back with a count of 6?

Instead of col, use ','+col+','

Madhivanan

Failing to plan is Planning to fail

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2009-04-28 : 08:47:37

ok, this is really weird now, anyone know why i'm getting some things counted and some not?, here's my results table when i run this query

columns are 'comments', col0, col1, col2 and relevanceCount (left to right below)

Torque control using the Optidrive Plus 2 1 0 3
Optidrive Plus 3GV User Guide 2 0 0 2
Optidrive E2 User Guide 2 0 0 2

here is the query i run to get this result

SELECT comments, (LEN(comments) - LEN(REPLACE(REPLACE(comments, ' ', '#'), 'optidrive', ''))) / 4 AS col0, (LEN(comments)
- LEN(REPLACE(REPLACE(comments, ' ', '#'), 'plus', ''))) / 4 AS col1, (LEN(comments) - LEN(REPLACE(REPLACE(comments, ' ', '#'), '3gv', ''))) / 4 AS col2,
((LEN(comments) - LEN(REPLACE(REPLACE(comments, ' ', '#'), 'optidrive', ''))) / 4 + (LEN(comments) - LEN(REPLACE(REPLACE(comments, ' ', '#'), 'plus',
''))) / 4) + (LEN(comments) - LEN(REPLACE(REPLACE(comments, ' ', '#'), '3gv', ''))) / 4 AS relevanceCount
FROM tblDLFiles
ORDER BY relevanceCount DESC

here is what i should be getting
Torque control using the Optidrive Plus 1 1 0 2
Optidrive Plus 3GV User Guide 1 1 1 3
Optidrive E2 User Guide 1 0 0 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-28 : 08:55:20

See my previous suggestion

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources