Step 2 uses nPath to create total counts.
CREATE MULTISET TABLE attrtot AS ( SELECT ( IND_EMLOP || IND_CLKNI || IND_CLKIN || IND_CLKAC || IND_CLKCO) AS IND, num_tot FROM ( SELECT case when num_EMLOP > 0 then '1' ELSE '0' END AS IND_EMLOP , CASE WHEN num_CLKNI > 0 then '1' ELSE '0' END AS IND_CLKNI , CASE WHEN num_CLKIN > 0 then '1' ELSE '0' END AS IND_CLKIN , CASE WHEN num_CLKAC > 0 then '1' ELSE '0' END AS IND_CLKAC , CASE WHEN num_CLKCO > 0 then '1' ELSE '0' END AS IND_CLKCO , COUNT (intractn_path) AS num_tot FROM nPath@coprocessor ( ON attrib PARTITION BY ind ORDER BY ts USING MODE (nonoverlapping) PATTERN ('(B|C|D|E|F)+') SYMBOLS ( int_type = 'EMLOP' AS B, int_type = 'CLKNI' AS C, int_type = 'CLKIN' AS D, int_type = 'CLKAC' AS E, int_type = 'CLKCO' AS F) RESULT ( FIRST(ind of ANY(B,C,D,E,F)) AS ind, COUNT (int_type of B) AS num_EMLOP, COUNT (int_type of C) AS num_CLKNI, COUNT (int_type of D) AS num_CLKIN, COUNT (int_type of E) AS num_CLKAC, COUNT (int_type of F) AS num_CLKCO, ACCUMULATE (int_type OF ANY(B,C,D,E,F) ) AS intractn_path ) ) AS dt GROUP BY 1,2,3,4,5 ) a ) WITH DATA;
SELECT * FROM attrtot ORDER BY ind;
IND num_tot ----- ------- 00001 138 00010 148 00011 37 00100 138 00101 30 00110 36 00111 7 01000 150 01001 39 01010 31 01011 13 01100 33 01101 19 01110 8 01111 8 10000 138 10001 30 10010 31 10011 11 10100 31 10101 10 10110 8 10111 1 11000 35 11001 6 11010 9 11011 6 11100 10 11101 5 11110 8 11111 3