Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Count inside aGroup_Concat

Is it possible to count occurences of strings like ‘pass’ and ‘fail’ inside a Group_Concat()?

I have the following situation there I need to count the occurences of ‘pass’ and ‘fail’
I need to count how often ‘pass’ and ‘fail’ occurs in

GROUP_CONCAT(parameters.calc_value0 ORDER BY samples.id ASC SEPARATOR 'n')

Here is my current situation so far

SELECT parametertypes.name as 'Test',

CASE   
   WHEN (IFNULL(GROUP_CONCAT(parameters.calc_value0 ORDER BY samples.id ASC SEPARATOR 'n'), (GROUP_CONCAT(parameters.value0 ORDER BY samples.id ASC SEPARATOR 'n'))) LIKE '%pass%')
    THEN CONCAT('<span class="green">', if(IFNULL(parameters.calc_value0, parameters.value0) LIKE '%pass%', COUNT(samples.id), COUNT(samples.id)) , ' pass </span>', CHAR(10), GROUP_CONCAT(DISTINCT CONCAT(samples.DDF_Artikelbezeichnung) ORDER BY samples.name ASC SEPARATOR 'n')) 
   
   ELSE '-'
END as 'Samples Pass',

CASE    
    
   WHEN (IFNULL(GROUP_CONCAT(parameters.calc_value0 ORDER BY samples.id ASC SEPARATOR 'n'), (GROUP_CONCAT(parameters.value0 ORDER BY samples.id ASC SEPARATOR 'n'))) LIKE '%fail%')
    THEN CONCAT('<span class="red">', if(IFNULL(parameters.calc_value0, parameters.value0) LIKE '%fail%', COUNT(samples.id), COUNT(samples.id)), ' fail </span>', CHAR(10), GROUP_CONCAT(DISTINCT CONCAT(samples.DDF_Artikelbezeichnung) ORDER BY samples.name ASC SEPARATOR 'n'))
  
   ELSE '-'
END as 'Samples Fail',

CASE     
   WHEN (IFNULL(GROUP_CONCAT(parameters.calc_value0 ORDER BY samples.id ASC SEPARATOR 'n'), (GROUP_CONCAT(parameters.value0 ORDER BY samples.id ASC SEPARATOR 'n'))) LIKE '%documentation%')
    THEN CONCAT('<span class="orange">', if(IFNULL(parameters.calc_value0, parameters.value0) LIKE '%documentation%', COUNT(samples.id), COUNT(samples.id)), ' documentation </span>',CHAR(10), samples.DDF_Artikelbezeichnung)      
  
   ELSE '-'
END as 'Samples Documentation'


FROM samples  
JOIN parameters ON parameters.sample_id = samples.id
JOIN parametertypes ON parametertypes.id = parameters.parametertype_id 

WHERE samples.order_id IN(1268)
AND samples.visible_to != 'deleted'
AND parameters.calc_value0 IS NOT NULL 

GROUP BY parametertypes.id -- , samples.DDF_Artikelbezeichnung

This are my current results

This are my current results. There should be 9 pass and 1 fail for first row.

Tags:

2

Answers


  1. 0

    When you need to count the amount of definite substring occurences in some string value then use the next pattern:

    SELECT (LENGTH(@string) - LENGTH(REPLACE(@string, @substring, ''))) / (LENGTH(@substring)) AS amount
...

    where @string is a value/column/expression to be investigated, and @substring is a substring value/column/expression to be searched for.

    This method does not count the overlapped substrings – for example, counting for 'abab' in xxabababyy will produce 1 and not 2.

    If you need to count for more than one substring then count for each substring separately.

    Login or Signup to reply.
  2. 0

    Here, in your case, we can use conditional aggregation to count the occurrences of pass and fail. Use the following query please:

    SELECT
    parametertypes.name AS 'Test',
    CONCAT(
        '<span class="green">',
        SUM(CASE WHEN parameters.calc_value0 = 'pass' THEN 1 ELSE 0 END),
        ' pass </span>',
        CHAR(10),
        GROUP_CONCAT(DISTINCT samples.DDF_Artikelbezeichnung ORDER BY samples.name ASC SEPARATOR 'n')
    ) AS 'Samples Pass',
    CONCAT(
        '<span class="red">',
        SUM(CASE WHEN parameters.calc_value0 = 'fail' THEN 1 ELSE 0 END),
        ' fail </span>',
        CHAR(10),
        GROUP_CONCAT(DISTINCT samples.DDF_Artikelbezeichnung ORDER BY samples.name ASC SEPARATOR 'n')
    ) AS 'Samples Fail',
    CONCAT(
        '<span class="orange">',
        SUM(CASE WHEN parameters.calc_value0 = 'documentation' THEN 1 ELSE 0 END),
        ' documentation </span>',
        CHAR(10),
        GROUP_CONCAT(DISTINCT samples.DDF_Artikelbezeichnung ORDER BY samples.name ASC SEPARATOR 'n')
    ) AS 'Samples Documentation'
FROM
    samples
    JOIN parameters ON parameters.sample_id = samples.id
    JOIN parametertypes ON parametertypes.id = parameters.parametertype_id
WHERE
    samples.order_id IN (1268)
    AND samples.visible_to != 'deleted'
    AND parameters.calc_value0 IS NOT NULL
GROUP BY
    parametertypes.id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search