SQL sum inside the value

Question

i have this kind of result but i want to sum up some of the value inside the value. Sorry for my english. Refer image for better understanding.

Expected Result:

My sql query :

     SELECT
            itmnocate.Source as Local,
            itmnocate.GradeCategory,
            sum(sales_data.QUANTITY/1000) AS UnitMT
       FROM
            sales_data
 INNER JOIN itmnocate 
         ON sales_data.ITEM = itmnocate.ItemNumber
      WHERE
            sales_data.unit = 'KG'
        AND
            sales_data.CUSTOMERACCOUNT not in ('CT1008','CT1009')
        AND itmnocate.Source in ('local','by product')
   GROUP BY itmnocate.GradeCategory

Answer 1

You can do what you want using a CASE expression to calculate the new category values:

SELECT 'LOCAL' as Local,
       (CASE WHEN i.GradeCategory = 'SUPER 15' THEN 'SUPER 15'
             ELSE 'OTHERS'
        END) as Cat,
       SUM(s.QUANTITY/1000) AS UnitMT
FROM sales_data s INNER JOIN
     itmnocate i
     ON s.ITEM = i.ItemNumber
WHERE s.unit = 'KG' AND
      s.CUSTOMERACCOUNT NOT IN ('CT1008', 'CT1009') AND
      i.Source in ('local', 'by product')
GROUP BY cat;

Answer 2

WITH your_query
 AS (SELECT 'BY PRODUCT' AS local,
            'BY PRODUCT' AS "GradeCategory 1",
            3380.59 AS unitmt

     UNION ALL
     SELECT 'LOCAL' AS local,
            'LOCAL OTHERS' AS "GradeCategory 1",
            2754.19 AS unitmt

     UNION ALL
     SELECT 'LOCAL' AS local,
            'SUPER 15' AS "GradeCategory 1",
            19598.17 AS unitmt

     UNION ALL
     SELECT 'LOCAL' AS local,
            'TENDER/RAMPASAN' AS "GradeCategory 1",
            53.65 AS unitmt
     )
select MAX(local), sub_category,SUM(unitmt) from (SELECT local,
   "GradeCategory 1",
   unitmt,
   CASE "GradeCategory 1" WHEN 'SUPER 15' THEN 'SUPER 15' ELSE 'LOCAL 
OTHERS' END
       AS sub_category
 FROM your_query) processd_data  group by sub_category