EN VI

Mysql - Count # of valus that exist in column string?

How to 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

Solution:

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.

Tags