MySql: count number of times a word is in a cell and put the number beside the word in bra

Question

I need the table to have:

accountprefix needs to have 'Oil" (####) for the count of how many times 'Oil' is in that cell

SELECT
  jurisdiction,
  Replace(REPLACE(REPLACE(GROUP_CONCAT(DISTINCT LEFT(fileno, 1)ORDER BY fileno DESC SEPARATOR ', '),'O','Oil')'M','Mobile'),'R','Real')AS accountprefix
From
  table.tester
WHERE
  statuscode = 'A'
  AND builddateid = '2023'
GROUP BY jurisdiction;

accountprefix needs to have 'Oil" (####) for the count of how many times 'Oil' is in that cell

Answer 1

Try the following where I modified the query to count and display the number of times 'Oil' appears in the accountprefix by replacing 'O' with 'Oil (count)' where the count represents the occurrences of 'O' in the fileno column for each jurisdiction.

SELECT
  jurisdiction,
  REPLACE(
    REPLACE(
      REPLACE(
        GROUP_CONCAT(DISTINCT LEFT(fileno, 1) ORDER BY fileno DESC SEPARATOR ', '),
        'O', CONCAT('Oil (', COUNT(CASE WHEN LEFT(fileno, 1) = 'O' THEN 1 END), ')')
      ),
      'M', 'Mobile'
    ),
    'R', 'Real'
  ) AS accountprefix
FROM
  table.tester
WHERE
  statuscode = 'A'
  AND builddateid = '2023'
GROUP BY jurisdiction;