0

If have the following field in my database 'usage'

^hoflaeden^,^obst^
^hotels^,^steuern^,^umweltmarketing^,^wochenmaerkte^
^messen^
^wochenmaerkte^
^hoflaeden^,^obst^,^wochenmaerkte^
^messen^,^kultur^
^baecker^
^kaffee^
^hotels^,^messen^
^hoflaeden^,^baecker^
^einzelhandel^
^supermaerkte^
^einzelhandel^,^messen^
^hotels^,^messen^,^einzelhandel^
^umweltmarketing^,^messen^
^hoflaeden^
^einzelhandel^,^kultur^,^messen^,^steuern^,^umweltmarketing^
^baecker^,^kaffee^
^wochenmaerkte^,^umweltmarketing^
^apotheken^
^einzelhandel^,^hotels^,^messen^,^umweltmarketing^
^einzelhandel^,^hoflaeden^,^messen^,^obst^,^wochenmaerkte^
^obst^
^steuern^
^apotheken^,^messen^
^baecker^,^hotels^,^obst^
^obst^,^hoflaeden^
^umweltmarketing^,^einzelhandel^
^obst^,^wochenmaerkte^
^einzelhandel^,^umweltmarketing^,^gemeinschaft^
^kultur^,^messen^
^supermaerkte^,^einzelhandel^
^hotels^,^umweltmarketing^
^baecker^,^hotels^,^kaffee^,^umweltmarketing^
^einzelhandel^,^hotels^,^kultur^,^messen^,^steuern^
^einzelhandel^,^messen^,^gemeinschaft^
^hotels^,^messen^,^umweltmarketing^
^supermaerkte^,^hoflaeden^,^messen^
^einzelhandel^,^umweltmarketing^
^umweltmarketing^,^messen^,^kultur^
^hotels^
^einzelhandel^,^kaffee^,^obst^,^umweltmarketing^,^wochenmaerkte^
^baecker^,^hoflaeden^,^kaffee^,^supermaerkte^
^messen^,^kultur^,^gemeinschaft^
^kultur^
^hoflaeden^,^obst^,^wochenmaerkte^,^gemeinschaft^
^messen^,^einzelhandel^
^einzelhandel^,^kultur^,^messen^
^hotels^,^messen^,^steuern^,^umweltmarketing^

and some more entries. I'd need count all ids that have distinct values of these fields. For example:

ID: 1 has ^hoflaeden^,^obst^,^wochenmaerkte^
ID: 2 has ^obst^,^wochenmaerkte^
ID: 3 has ^supermaerkte^,^hoflaeden^,^messen^

So the result should be:

^hoflaeden^ : 2
^obst^ : 2
^wochenmaerkte^: 2
^supermaerkte^: 1
^messen^: 1

All values are enclosed by ^ and seperated by ,

How would the query look like?

Antivist
  • 184
  • 9
  • Use this solution [SQL split values to multiple rows](https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows) and then `COUNT()` and `GROUP BY` – Valerica Dec 12 '17 at 15:05
  • Thank you, didn't find that post. Will have a look into tomorrow. – Antivist Dec 12 '17 at 15:20
  • 1
    As has been said so often: In a relational database it's a very bad idea to store a list of values in a string. If interested in the separate parts, either store them separately or leave the DBMS oblivious to this and solve it in your app instead. – Thorsten Kettner Dec 12 '17 at 15:40
  • You can also base your solution on [this one](https://stackoverflow.com/questions/46869350/selecting-the-substring-with-the-highest-count-in-a-column-where-the-substrings/46869787#46869787). That is, if you use mySql 8.0. – itsLex Dec 12 '17 at 18:14

0 Answers0