Mysql Data Grouping and Regular Expression

Question

I have a table where codes are registered by our suppliers after examining the patients. Codes look like A11, B99, C68, A12, O87, F76, etc. Now I need to create a report in which these codes are grouped and counted. Groups should look like A00-B99, C00-D48, D50-D89, etc. I researched and found that I might have to use REGEXP, which I want to avoid as far as I can. Can someone please help, what is an efficient and optimized way to do this?

Table:

 ID  Codes   description
  1  A11     Infection
  2  A01     Intestinal infectious diseases   
  3  H77     Intestinal infectious diseases    
  5  D98     Intestinal infectious diseases      
  6  D98     Intestinal infectious diseases
  7  A11     Intestinal infectious diseases
  8  A00     Intestinal infectious diseases
  9  A03     Intestinal infectious diseases
  10  D00     Intestinal infectious diseases   
11  D98     Intestinal infectious diseases
... 
...
...
...

Desired Result

code_group    Count
A00-B99       10
C00-D48       50
D50-D89       100  
...
...
...

Hope I explained this question.

Thanks for any help!

Answer 1

case :

select (case when code between 'A00' and 'A99' then 'A00-A99'
             when code between 'B00' and 'C48' then 'B00-C48'
             when code between 'C49' and 'D99' then 'C49-D99'
             . . .
        end) as codeGroup,
       count(*)
from t
group by codeGroup
order by codeGroup;

Answer 2

MySQL LIKE, , . - :

SELECT "A00-A99" AS `Code Group`, COUNT(*) as `Group Count` FROM table_name as T WHERE T.Codes LIKE "A%"

Code Group   Group Count
A00-A99      44

:

SELECT "B00-C48" AS `Code Group`, COUNT(*) as `Group Count` 
FROM table_name as T 
WHERE T.Codes LIKE "B%" 
OR (T.Codes LIKE "C%" AND SUBSTRING(T.Codes,2) <= 48)

SUBSTRING(): https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring

, UNION ALL :

(SELECT "A00-A99" ... )
UNION ALL
(SELECT "B00-C48" ... )
UNION ALL
...

, .

Answer 3

, . : A00-B99, C00-D48, D50-D89,

code_group    Count
A00-A99       10
B00-C48       50
C49-D99       100 

therefore, you used the wrong desired result group A00-A99 , C49-D99 , because they do not correspond to the ranges that you previously announced.

Here is an example of how to group a table with A00-A99; B00-B99 ...:

http://sqlfiddle.com/#!9/63229/3

SELECT 
    CONCAT(LEFT(codes,1),'00-',LEFT(codes,1),'99') gr ,
    COUNT(*)
FROM myTable
GROUP BY LEFT(codes,1);

But if you really need these weird ranges as a group, I suggest you set up another table, for example:

http://sqlfiddle.com/#!9/3eca4/3

CREATE TABLE myGroups (
  id INT AUTO_INCREMENT PRIMARY KEY, 
  minGr varchar(10), 
  maxGr varchar(10)
 );

and your request could be:

SELECT 
    CONCAT(g.minGr,'-',g.maxGr) gr ,
    COUNT(*)
FROM myTable t
INNER JOIN myGroups g
ON t.codes>=g.minGr
   AND t.codes<=g.maxGr
GROUP BY g.id
ORDER BY g.id;