CODEWITHSUNDEEP
mysqlsql
chomsky
chomsky

Reputation: 300

count of distincts substring in query

I'm trying to create a query that count (or at least retrieve) distincts occurrences of a substring in a column.

I have one column that is like this:

elem1=value1|elem2=value2|elem3=value3

I want to retrieve distincts values of elem2 and the number of ocurrences.

SELECT
    substring(
        column , LOCATE( 'elem2=' , column ) + $1 , $2 
    ) AS a ,
    COUNT(*) b ,
FROM
   column ORDER BY a;

Thanks

Upvotes: 0

Views: 1582

Answers (1)

spencer7593
spencer7593

Reputation: 108510

Here's one way to achieve the specified result.

SELECT COUNT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(t.col,'elem2=',-1),'|',1)) FROM t WHERE t.col LIKE '%elem2=%'

Apparently, I misread the question (the query above returns a count of distinct values. Or, I just lost track of the specified result, while I was working on the tedious string parsing.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.col,'elem2=',-1),'|',1) AS a
     , COUNT(*) AS b
  FROM t
 WHERE t.col LIKE '%elem2=%'
 GROUP BY SUBSTRING_INDEX(SUBSTRING_INDEX(t.col,'elem2=',-1),'|',1)
 ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(t.col,'elem2=',-1),'|',1)

NOTE:

Note that SQL wasn't really designed for parsing values out of strings.

The normative relational pattern for this type of data would to create a table, with columns named elem1, elem2, elem3, and to store each separate value in a column.

For example:

CREATE TABLE t 
( id     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, elem1  VARCHAR(80)
, elem2  VARCHAR(80)
, elem3  VARCHAR(80)
);
INSERT INTO t (elem1, elem2, elem3) VALUES ('value1', 'value2', 'value3');

To get a count of distinct values in elem2 column, we'd do something like this.

SELECT COUNT(DISTINCT elem2)
  FROM t

To get the distinct values of elem2 along with the count of occurrences

SELECT elem2    AS a
     , COUNT(*) AS b
  FROM t
 GROUP BY elem2
 ORDER BY elem2

That's essentially the same as the query at the beginning of my answer; the first query just has to do the parsing the 'elem2=value2' out of the string.

Upvotes: 3

Related Questions