I have a table in the following format:
| id | tags |
|----|-------------------------|
|1 |['Car', 'Plane', 'Truck']|
|2 |['Plane', 'Truck'] |
|3 |['Car', 'Plane'] |
|4 |['Plane'] |
|5 |['Boat', 'Truck'] |
How can I create a table that gives me the total number of occurrences of each item in all cells of the "tags" column? Items ideally do not include single quotes, but may if necessary.
The resulting table would look like:
| tag | count |
|-------|-------|
| Car | 2 |
| Plane | 4 |
| Truck | 3 |
| Boat | 1 |
The following does not work because it only counts identical "tags" entries rather than comparing list contents.
SELECT u.id, count(u.tags) as cnt
FROM table u
group by 1
order by cnt desc;
I am aware of this near-identical question, but they are using Snowflake/SQL whereas I am using MySQL/Grafana so the accepted answer uses functions unavailable to me.
