How do I count individual text count from a column from all records

Question

1.00/5 (1 vote)

See more:

Hai,

I need to display count of each text occurrence on all rows from a particular column.

See result below:

-------------------------------------
Tags                |  Total        |
--------------------|---------------|
keyword.io          |     34        |
------------------------------------|
keywordtool.io ,    |     45        |
------------------------------------|
seobook.com ,       |     56        |
------------------------------------|
adwords.google.com  |     123       |
-------------------------------------

I need exactly like above.

Here I have a set of Tags, I need to display count each tag as column in the name 'Total'
If want to understand I have posted the same @ C# Corner with source code file, please make a visit to it.
Please suggest your queries to get my desired result.

Thank in Advance.

What I have tried:

SQL

DECLARE @tags VARCHAR(8000) 
DECLARE @tot INT
select @tags = coalesce(@tags + ',' , ' ') + Labels from addNew 
select @tot = count(@tags)
select a.Labels as Tags,@tot as Total from addNew a 
inner join addNew n
on a.Labels = n.Labels
group by a.Labels

I Got the result as:

-------------------------------------
Tags                |  Total        |
--------------------|---------------|
keyword.io,,        |               |
keywordtool.io,     |      1        |
seobook.com,        |               |
Asp.Net             |               |
adwords.google.com  |               |
------------------------------------|
HtmlAgilityPack     |               |
library,            |               |
Parse Web Page,     |      1        |
Asp.Net,            |               |
Meta Descrition     |               |
------------------------------------|
seobook.com,        |               |
aptitude,           |               |
algebra,            |     1         |
------------------------------------|
adwords.google.com  |     1         |
-------------------------------------

Posted 5-Oct-17 8:55am

cgprakash

Updated 8-Oct-17 22:29pm

v2

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santosh kumar Pithani · Answer 1 · 2017-10-08T19:34:00

Solution 3

SELECT AN.Labels,
       Tag      , 
       Total  FROM AddNew AN 
 CROSS APPLY 
(
SELECT VALUE   as  Tag,
       count(*)as Total FROM string_split(AN.Labels,',') 
  GROUP BY VALUE
)AS ff

Posted 8-Oct-17 19:34pm

Santosh kumar Pithani

Comments

Maciej Los 9-Oct-17 2:13am

string_split(,) is not inbuilt sql function. So, you have to share its content.

Santosh kumar Pithani 9-Oct-17 2:56am

Thank you Maciej,some old versions doesn't support String_split(,) for those below link is help full to create String_split function.
https://sqlperformance.com/2016/03/sql-server-2016/string-split.

Maciej Los · Answer 2 · 2017-10-08T20:47:00

Check this:

SQL

DECLARE @tmp TABLE(Tags NVARCHAR(255))

INSERT INTO @tmp (Tags)
VALUES('keyword.io,keywordtool.io,seobook.com,Asp.Net,adwords.google.com'), 
('HtmlAgilityPack library,Parse Web Page,Asp.Net,Meta Descrition'),
('seobook.com,aptitude,algebra'),
('adwords.google.com')


;WITH CTE AS
(
	SELECT LEFT(Tags, CHARINDEX(',', Tags)-1) AS Tag, RIGHT(Tags, LEN(Tags)-CHARINDEX(',', Tags)) AS Remainder
	FROM @tmp
	WHERE CHARINDEX(',', Tags) >0
	UNION ALL
	SELECT LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS Tag, RIGHT(Remainder, LEN(Remainder)-CHARINDEX(',', Remainder)) AS Remainder
	FROM CTE
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT Remainder As Tag, NULL AS Remainder
	FROM CTE
	WHERE CHARINDEX(',', Remainder)=0
)
SELECT Tag, COUNT(Tag) As CountOfTag
FROM CTE
GROUP BY Tag

C#

DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("Tags", typeof(string)));
dt.Rows.Add(new object[]{"keyword.io,keywordtool.io,seobook.com,Asp.Net,adwords.google.com"});
dt.Rows.Add(new object[]{"HtmlAgilityPack library,Parse Web Page,Asp.Net,Meta Descrition"});
dt.Rows.Add(new object[]{"seobook.com,aptitude,algebra"});
dt.Rows.Add(new object[]{"adwords.google.com"});


var data = dt.AsEnumerable()
	.SelectMany(v=>v.Field<string>("Tags").Split(new string[]{","}, StringSplitOptions.RemoveEmptyEntries))
	.GroupBy(x=>x)
	.Select(grp=> new
		{
			Tag = grp.Key,
			Count = grp.Count()
		});

foreach(var t in data)
{
	Console.WriteLine("{0}\t{1}", t.Tag, t.Count);
}

In both cases the result is the same:

keyword.io  1
keywordtool.io  1
seobook.com  2
Asp.Net  2
adwords.google.com  2
HtmlAgilityPack library  1
Parse Web Page  1
Meta Descrition  1
aptitude  1
algebra  1

Good luck!

Santosh kumar Pithani · Answer 3 · 2017-10-08T22:29:00

SQL

DECLARE @tmp TABLE(Tags NVARCHAR(255))
 INSERT INTO @tmp (Tags)
VALUES('keyword.io,keywordtool.io,seobook.com,Asp.Net,adwords.google.com'), 
('HtmlAgilityPack library,Parse Web Page,Asp.Net,Meta Descrition'),
('seobook.com,aptitude,algebra'),
('adwords.google.com')
select tag,count(*) as cnt from @tmp a 
 cross apply (
 select VALUE as tag  from string_split(a.Tags,',') 
 )as ff
group by tag;

My past query belongs to each row so this query for all rows in column.

adwords.google.com	2
algebra	1
aptitude	1
Asp.Net	2
HtmlAgilityPack library	1
keyword.io	1
keywordtool.io	1
Meta Descrition	1
Parse Web Page	1
seobook.com	2

Santosh kumar Pithani · Answer 4 · 2017-10-06T21:21:00

Solution 2

Select 
Labels as Tags ,
Count(1) as Total
from AddNew Group by Labels.

Posted 6-Oct-17 21:21pm

Santosh kumar Pithani

Comments

cgprakash 8-Oct-17 10:35am

hi Santosh, thnx for reply.
What i need exactly is to display count of each Labels in all columns:
for eg:
consider in first row it contains following values:

LABELS
--------------------------------------
keyword, keyword , seobook , adwords |
--------------------------------------

From the above instance, the word "keyword" comes two times and remaining are one time.

the result I need is:

TAGS    | TOTAL|
----------------
keywords| 2    |
seobook | 1    |
adwords | 1    |
----------------

Santosh kumar Pithani 9-Oct-17 1:41am

Hello Prakash,i hope your problem is solved now by new query.Thank your for giving replay.

Maciej Los 9-Oct-17 2:11am

Sorry, but your solution is wrong. You have to split tags by comma then you'll be able to count tags.

Santosh kumar Pithani 9-Oct-17 2:26am

Have you executed that query?

Maciej Los 9-Oct-17 2:51am

Yeah, i've tried. As i mentioned, this solution is wrong. It returns:


adwords.google.com	1
HtmlAgilityPack library,Parse Web Page,Asp.Net,Meta Descrition	1
keyword.io,keywordtool.io,seobook.com,Asp.Net,adwords.google.com	1
seobook.com,aptitude,algebra	1

instead of each tag separately.

Santosh kumar Pithani 9-Oct-17 4:32am

i Wrote query according to above what prakash has mensioned.