mySQL unique values

Question

I need to get unique values from a table. I have a single column with comma separated keywords. I need to derive a single list of all the keywords without duplicates. Getting the count of how often each keyword is present, too.

From what I have researched, it is an UNPIVOTING like function with an unknown number of columns?

For example:

keywords

red, blue, yellow
blue, orange, black, white
brown, black, clear, pink
blue, violet, orange

Result

color | count

red 1
blue 3
yellow 1
orange 2
black 2
white 1
brown 1
clear 1
pink 1
violet 1

Thank you in advance!!

** Thus far I have tried adding an explode_table type procedure, but realized I can't call that dynamically from a View. Then I have been experimenting with performing a reverse GROUP_CONCAT() on the column. I haven't been able to produce code that performs.

My version of echo_Me's answer:

    SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(sKeywords, ',', n.n), ',', -1) value , count(*) as counts
FROM tblPatternMetadata t CROSS JOIN 
    (SELECT a.N + b.N * 10 + 1 n FROM (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL  SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a, (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION  ALL SELECT 9) b
    ORDER BY n ) n
    WHERE n.n <= 1 + (LENGTH(sKeywords) - LENGTH(REPLACE(sKeywords, ',', ''))) group by value

post the query that you tried

Slowcoder
– Slowcoder

2014-06-26 16:44:56 +00:00
Commented Jun 26, 2014 at 16:44 — Slowcoder
– Slowcoder, Commented Jun 26, 2014 at 16:44

echo_Me · Accepted Answer · 2014-06-26 17:11:43Z

1

Try that:

    SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(t.keywords, ',', n.n), ',', -1) value , count(*) as counts
    FROM table1 t CROSS JOIN 
   (
   SELECT a.N + b.N * 10 + 1 n
   FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL  SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION  ALL SELECT 9) b

   ORDER BY n
 ) n
 WHERE n.n <= 1 + (LENGTH(t.keywords) - LENGTH(REPLACE(t.keywords, ',', '')))
 group by value

DEMO HERE

answered Jun 26, 2014 at 17:11

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

kobalt_monkey Over a year ago

Thank you for the answer. A bit over my head, but I'm trying it out. I inserted my column name and try running it in Navicat. I get a 'View's SELECT contains a subquery in the FROM clause' error. I'll post my version of your statement in my question above.

echo_Me Over a year ago

whats wrong ? it works good in my demo , you must have done something wrong.

kobalt_monkey Over a year ago

The code works great in the DEMO. And I can force phpMyAdmin and Navicat to run the statement. But if I try to save the statement into a view, it returns that error. argh.

kobalt_monkey Over a year ago

I just read about mySQL not allowing subqueries. Which explains the error "View's SELECT contains a subquery in the FROM clause". I wonder if it is possible to create a view from the subquery and then call that view in the original query?

echo_Me Over a year ago

you have other solution , is to reorganize your table and put the values separatly with the count. colors and counts . then you dont need subquery , you just need simple query. you have bad structure in your table. dont store values comma separated .

|

Collectives™ on Stack Overflow

mySQL unique values

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related