Counting word occurrence with SQL query
You can use count() over()
:
select distinct t1.id,
t1.sentence,
coalesce(t2.cnt, 0) cnt
from table1 t1
left join
(
select t1.id,
t1.sentence,
t2.word,
count(t2.word) over(partition by t1.id, t2.word) cnt
from table1 t1
left join table2 t2
on t1.id = t2.id
) t2
on t1.id = t2.id
and t2.cnt > 1
order by t1.id
See SQL Fiddle with Demo.
Or you can just use count()
:
select t1.id,
t1.sentence,
coalesce(t2.cnt, 0) cnt
from table1 t1
left join
(
select t1.id,
t1.sentence,
t2.word,
count(t2.word) cnt
from table1 t1
left join table2 t2
on t1.id = t2.id
group by t1.id, t1.sentence, t2.word
having count(t2.word) > 1
) t2
on t1.id = t2.id
order by t1.id
See SQL Fiddle with Demo
SQL count the occurrences of words from one table in second table
Simple version:
SELECT id,
status,
(SELECT (SUM(CHAR_LENGTH(request)) -
SUM(CHAR_LENGTH(REPLACE(request, id, ''))))
/ CHAR_LENGTH(id) FROM request_records) AS occurrence_count,
(SELECT COUNT(*)
FROM request_records
WHERE INSTR(request, id) > 0) AS record_count
FROM id_status
See updated SQL fiddle.
This just looks for the word being present anywhere so for example would blindly count abcsessions
or sessionsxyz
when looking for sessions
. If something more complex is needed (e.g. considering word boundaries), you might need to use a regular expression replacer rather than the REPLACE
function - but unfortunately this is one thing MySQL lacks. I once had a go at writing one but it's not trivial - see here.
MS SQL - Count the occurrences of a word in a field
There is a STRING_SPLIT support from sql-server 2016 if your version lower than 2016,you can try to write a split function to split your column by ,
CREATE FUNCTION fn_split ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
then use CROSS APPLY
to get count
by the name.
SELECT Name,count(*)
FROM T t1 CROSS APPLY fn_split(t1.col) v
group by Name
sqlfiddle
How to count the occurrences of words from one table to comments in another table
how to count occurrences of multiple words in a sql query
If you want to count the number of sentences that contain the words, you can use:
select w.*,
(select count(*)
from sentences s
where s.sentence like concat('%', w.word, '%')
) as cnt
from words w
where weightage > 5;
This makes some assumptions on what you really mean.
- "how many times each word is used in the sentence table" means "how many sentences contain the word at least once".
- "used" can be handled by looking for the word anywhere in the sentence, regardless of surrounding characters.
Count the occurrences of a group of words in a text column in SQL
Using STRTOK_SPLIT_TO_TABLE:
Tokenizes a string with the given set of delimiters and flattens the results into rows.
SELECT c.CONTENT_ID, c.DESCRIPTION
,COUNT_IF(k.TOPIC = 'nature') AS nature
,COUNT_IF(k.TOPIC = 'utensils') AS utensils
,COUNT_IF(k.TOPIC = 'animal') AS animals
FROM content c
,LATERAL STRTOK_SPLIT_TO_TABLE(c.description, '(),. ') s
JOIN keyword_tbl k
ON TRIM(s.value) = k.words
GROUP BY c.CONTENT_ID, c.DESCRIPTION
ORDER BY c.CONTENT_ID;
Output:
To handle "leaf", "leafs" the join condition needs to be altered:
-- substring
ON TRIM(s.value) ILIKE k.words|| '%'
-- only 's'
ON TRIM(s.value) ILIKE ANY (k.words, k.words|| 's')
Output:
Related Topics
Add a Column to Specific Position in Mssql Server
Simple Check for Select Query Empty Result
Query to Calculate Average of Employee Salaries Working Under Manager in SQL
Node.Js Mssql Tedius Connectionerror: Failed to Connect to Localhost:1433 - Connect Econnrefused
Deleting Rows from Parent and Child Tables
How to View SQL Table Structure in Oracle SQL Developer
Mysql Query - Records Between Today and Last 30 Days
Row Numbers in Query Result Using Microsoft Access
Mysql in Xampp Starts and Stops in Five Seconds
Mysql - Move Rows from One Table to Another
How to Exclude Specific Row from Query in MySQL
How to Find the Row Count for All Your Tables in Postgres
How to Retrieve Microseconds or Milliseconds from MySQL Current Time
Jsonb Array Contains Like or and and Operators
Group MySQL Query by 15 Min Intervals