Count after removal of a certain character in column - SQL

Question

I have a table(staffs_table) with the following data;

name               profession
------------------------------
Corbin             Human Resources|Accountant
Cort               Programmer|Receptionist|Helpdesk

I want to count number of people per each profession so as my results to be as follows;

profession             number_of_staffs
---------------------------------------
Human Resources            1
Accountant                 1 
Programmer                 1
Receptionist               1
Helpdesk                   1

I tried the following SQL query with no success;

SELECT profession, COUNT(*) AS number_of_staffs FROM staffs_table GROUP BY profession ORDER BY COUNT(*) DESC;

OUTPUT:

profession                   number_of_staffs
---------------------------------------------
Human Resources|Accountant            1
Programmer|Receptionist|Helpdesk      1

Also tried the following;

SELECT staffs_table.name,
SUBSTRING_INDEX(SUBSTRING_INDEX(staffs_table.profession, '|', numbers.n), '|', -1) profession
FROM
  numbers INNER JOIN staffs_table
  ON CHAR_LENGTH(staffs_table.profession)
     -CHAR_LENGTH(REPLACE(staffs_table.profession, '|', ''))>=numbers.n-1

OUTPUT:

name               profession
------------------------------
Corbin             Human Resources
Corbin             Accountant
Cort               Programmer
Cort               Receptionist
Cort               Helpdesk

Thanks in advance!

Does this answer your question? [SQL split values to multiple rows](https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows) — Ankit Bajpai, Mar 02 '20 at 13:46
Fix your data model! Don't store multiple values in a single column! — Gordon Linoff, Mar 02 '20 at 13:54
@AnkitBajpai thanks for reply. it is different. The question you cited, only show results after removal of ` '|' ` without a count. My question is interested in counting. — cany, Mar 02 '20 at 16:13
So once you break it in rows you can then fire a cont function to count the keywords. — Ankit Bajpai, Mar 02 '20 at 16:14
@AnkitBajpai I tried that and it seems to have errors. The count keyword cannot be at the end — cany, Mar 02 '20 at 16:21
@Akina upgrading from mysql from 5.7 to 8.0. i will let you know — cany, Mar 02 '20 at 18:11
@GordonLinoff there is already an answer posted by Akina, check below — cany, Mar 02 '20 at 18:34

score 1 · Accepted Answer · answered Mar 02 '20 at 16:54

1

SELECT jsontable.profession, COUNT(jsontable.profession) number_of_staffs
FROM staffs_table
CROSS JOIN JSON_TABLE( CONCAT('["', REPLACE(staffs_table.profession, '|', '","'), '"]'),
                       "$[*]" COLUMNS( profession VARCHAR(254) PATH "$" )
                     ) AS jsontable
GROUP BY jsontable.profession;

fiddle

answered Mar 02 '20 at 16:54

Akina

39,301
5
14
25

Thanks for reply. I edited `jsontable.profession` to `staffs_table.profession` and tried to execute but it shows error near `JSON_TABLE` saying that table doesn't exist – cany Mar 02 '20 at 17:19
@canonngirwa *I edited* Why??? Why did you need to edit the **correct** query? – Akina Mar 02 '20 at 17:21
@canonngirwa This is "a tale about the error". Show FULL error message, without editions and restrictions. – Akina Mar 02 '20 at 17:26
` #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(CONCAT('["', REPLACE(staffs_table.profession, '|', '","'), '"]'),"$[*]" COLUMNS' at line 3 ` – cany Mar 02 '20 at 17:29
@canonngirwa What is your MySQL version? – Akina Mar 02 '20 at 17:30
version 5.7.24, running on windows – cany Mar 02 '20 at 17:37
@canonngirwa You should not have ignored my first question is about the MySQL server version... 8.0.4 or higher needed for successful using this code. It is not applicable to your version. – Akina Mar 02 '20 at 17:40
Thank you so much @Akina. It worked like charm. I added ` ORDER BY ` to make output look nice. Once again, thank you so much – cany Mar 02 '20 at 18:52

Count after removal of a certain character in column - SQL

1 Answers1