There is a machine which is made with multiple parts, those parts were of two different country, the need is to find percentage of parts used from each country.

select count(*) as "% of CH parts"
from tablename
where Parts_Used like 'CH%';

Used this but did not get result.

CodePudding user response：

Try this:

SELECT MP.[Machine]
      ,SUM(IIF(CHARINDEX('IN_', PU.[value]) > 0, 1, 0)) * 100.0 / COUNT(*) AS [% of IN part]
      ,SUM(IIF(CHARINDEX('CH_', PU.[value]) > 0, 1, 0)) * 100.0 / COUNT(*) AS [% of CH parts]
FROM machine_parts MP
CROSS APPLY STRING_SPLIT (Parts_Used, ',') PU
GROUP BY MP.[Machine]

The idea is to perform split of the values to now how may parts we have and easily perform the conditional counting with SUM and IIF. Basically, we have the following:

SELECT *
FROM machine_parts MP
CROSS APPLY STRING_SPLIT (Parts_Used, ',') PU

Then just counting.