0

Currently I have a table as my database, and I want to create a Bar Chart to of out the Reasons Column. This is an example of my table:

Table Name: Survey

id reasons
1 a,b,c
2 a,d,e
3 b,c,d

How to count total amount of each reasons like this table below?

reasons total
a 2
b 2
c 2
d 1
e 1
mstr_voda
  • 11
  • 3
  • If you fix your design, this is trivial. – Thom A Aug 23 '21 at 12:49
  • Does these answer your question? [Turning a Comma Separated string into individual rows](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows), [SQL find total count of each type in a column](https://stackoverflow.com/questions/32595456/sql-find-total-count-of-each-type-in-a-column) – Thom A Aug 23 '21 at 12:51
  • any suggestion to on how to fix it? – mstr_voda Aug 23 '21 at 12:52
  • *"any suggestion to on how to fix it?"* Look into the basic principles behind normalisation and one to many relationships. – Thom A Aug 23 '21 at 13:00

1 Answers1

1

You would use string_split():

select s.value as reason, count(*)
from t cross apply
     string_split(reasons, ',') s
group by s.value
order by s.value;

That said, you should fix your data model. You should have a separate table with one row per reason.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786