CODEWITHSUNDEEP
mysqlsqlcountunique
kobalt_monkey
kobalt_monkey

Reputation: 1

mySQL unique values

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

Result

color | count

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

Upvotes: 0

Views: 109

Answers (1)

echo_Me
echo_Me

Reputation: 37233

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

Upvotes: 1

Related Questions