Sql Count All Word Occurrences from a Table

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