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

Mysql – Count # of valus that exist in column string

I am trying to count # of rows where a value exists in any string of another table. Here is my example data:

ToolCode
Drill
Screwdriver
Saw
WorkCenter
Screwdriver1
Drill1
Saw2
Saw3
Screwdriver15
Drill 12
Saw10

So the totals would be:

Tool Code Quantity
Drill 2
Saw 3
Screwdriver 2

I attempted to use different count by len functions, but was unable to make it work. I am hoping for help in this matter and getting an example of what to do.

Select ToolCode, Len(ToolCode), Count(WorkCenter)
FROM JT_ToolCode, JT_WorkCenter
WHERE ToolCode = Left(Workcenter,Len(ToolCode))
Group By ToolCode, WorkCenter
Tags:

2

Answers


  1. 0

    While not efficient this would work by simply joining on a like.

    Demos

    Note: this assumes any occurrence of a toolcode in a workcenter is desired a like to the toolcode. however you could get false or double counts if you have tool codes like Drill and PneumaticDrill, as Drill will include PneumaticDrill in it’s count. as in this demo

    SELECT ToolCode, count(WorkCenter) WorkCenterCount 
FROM CTE1 --Change to your Name
LEFT JOIN CTE2 --Change to your tableName
 on CTE2.WorkCenter like concat('%', CTE1.ToolCode,'%')
GROUP BY  toolcode

    Or using an inline view which runs for each row in the first table getting a count from the second

    SELECT ToolCode, (SELECT Count(*) 
                  FROM CTE2 
                  WHERE CTE2.WorkCenter Like concat('%',CTE1.TOOLCODE,'%')) as WorkCenterCount 
FROM CTE1
ORDER BY ToolCOde

    Giving us:

    +-------------+-------------------+
|  ToolCode   | WorkCenterCount |
+-------------+-------------------+
| Drill       |                 2 |
| Saw         |                 3 |
| Screwdriver |                 2 |
+-------------+-------------------+
    Login or Signup to reply.
  2. 0

    You can try something like this:

    SELECT ToolCode.ToolCode, COUNT(*) AS Quantity
FROM ToolCode
JOIN WorkCenter ON WorkCenter.WorkCenter LIKE CONCAT('%', ToolCode.ToolCode, '%')
GROUP BY ToolCode.ToolCode;

    This query looks at both the ToolCode and WorkCenter tables. It checks if each tool is mentioned in any work center. Then, it counts how many times each tool appears in the work centers and groups the results by tool.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search