CODEWITHSUNDEEP
sqldatabaseazure-sql-databasessms
Nontaphat S.
Nontaphat S.

Reputation: 45

How to count each value in multiple rows and same column?

I have the problem to count the number of each tags.

For example:

    tags
0  a,b,c,d
1  a,b
2  c,d
3  a,c

My Expected Results:

tags   count
  a      3
  b      2
  c      3
  d      2

Upvotes: 1

Views: 84

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12969

You can use STRING_SPLIT to achieve this

SELECT
value, count(*) as countOfTags
FROM
(
VALUES
(0,  'a,b,c,d')
,(1,  'a,b'    )
,(2,  'c,d'    )
,(3,  'a,c'    )
) as t(id, tags)
CROSS APPLY 
string_split(tags,',')
GROUP BY value;

The result set

+-------+-------------+
| value | countOfTags |
+-------+-------------+
| a     |           3 |
| b     |           2 |
| c     |           3 |
| d     |           2 |
+-------+-------------+

Upvotes: 1

MT_dev
MT_dev

Reputation: 163

Welcome to StackOverFlow!

Do you have an example of your code? Your tag says "SQL" so that's what I'll assume. I can't tell much from your question right now as you haven't given us enough info to help. It would be nice for future questions to add a bit more detail as to what you've tried to do already.

Still, I found this post that's on counting occurrences of column values, so it could answer your question. You can always edit your questions for clarity if need be!

Upvotes: 0

Related Questions