SELECT areasymbol AS Area_symbol, musym AS Map_unit_symbol, muname AS Map_unit_name, mu.mukey/1 AS MUKEY, c.cokey/1 AS COKEY, compname AS Component_name, comppct_r AS Component_percent,
(SELECT TOP 1 ROUND (AVG(interphr) over(PARTITION BY interphrc),2)
FROM mapunit AS m1
INNER JOIN component AS c1 ON c1.mukey=m1.mukey
INNER JOIN cointerp AS ci1 ON c1.cokey = ci1.cokey AND m1.mukey = mu.mukey AND ruledepth = 0 AND mrulename LIKE
'NCCPI - National Commodity Crop Productivity Index (Ver 2.0)'
GROUP BY interphrc, interphr, comppct_r
ORDER BY SUM (comppct_r) over(PARTITION BY interphrc) DESC) AS Dominant_component_rating, interphrc AS Component_rating_class,
(SELECT TOP 1 interphrc
FROM mapunit AS m2
INNER JOIN component AS c2 ON c2.mukey=m2.mukey
INNER JOIN cointerp AS ci2 ON c2.cokey = ci2.cokey AND m2.mukey = mu.mukey AND ruledepth = 0 AND mrulename LIKE
'NCCPI - National Commodity Crop Productivity Index (Ver 2.0)'
GROUP BY interphrc, comppct_r
ORDER BY SUM(comppct_r) over(PARTITION BY interphrc) DESC) AS Dominant_component_class
FROM legend AS l
INNER JOIN mapunit AS mu ON mu.lkey = l.lkey AND l.areasymbol LIKE
'MO123'
INNER JOIN component AS c ON c.mukey = mu.mukey
INNER JOIN cointerp AS ci ON c.cokey = ci.cokey AND ruledepth = 0 AND mrulename LIKE
'NCCPI - National Commodity Crop Productivity Index (Ver 2.0)'
GROUP BY areasymbol, musym, muname, mu.mukey, c.cokey, compname, comppct_r, interphrc
ORDER BY areasymbol, musym, muname, mu.mukey, comppct_r DESC, c.cokey