Link to home
Start Free TrialLog in
Avatar of Massimo Scola
Massimo ScolaFlag for Switzerland

asked on

SQL Count Number of Occurrences in Column

We are using MariaDB at work and I have been asked to create a query that would involve counting the number of a particular string in a column.

User generated image
The table sumDataContent has a text column with HTML in it.
I have been asked to display all the Ids that have 1 or 2 <blockquotes> in it.

My query:

SELECT dataId, SUM(blockquoteCounts)
FROM (
	SELECT dataId, LENGTH(sumDataContent.textHTML) - LENGTH(REPLACE(sumDataContent.textHTML,"%<blockquote>%","")) AS COUNTS
	FROM sumDataContent) AS blockquoteCounts
GROUP BY dataId

Open in new window



Unfortunately, this doesn't work.

Can someone give me an idea of what I'm doing wrong?

Thanks

Massimo
Avatar of skullnobrains
skullnobrains
SELECT dataId, sum((LENGTH(sumDataContent.textHTML) - LENGTH(REPLACE(sumDataContent.textHTML,"%<blockquote>%",""))) / LENGTH("%<blockquote>%")) AS COUNTS
      FROM sumDataContent
GROUP BY dataId

query not tested so there may be an extra or missing parenthesis. but it should do the trick
Avatar of skullnobrains
skullnobrains
missed the 1 or 2 requirement

SELECT dataId, sum((LENGTH(sumDataContent.textHTML) - LENGTH(REPLACE(sumDataContent.textHTML,"%<blockquote>%",""))) / LENGTH("%<blockquote>%")) AS COUNTS
      FROM sumDataContent
GROUP BY dataId
HAVING COUNTS > 0 and COUNTS < 3
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.