SQL值在里面(SQL sum inside the value)

SQL值在里面(SQL sum inside the value)

我有这种结果，但我想总结价值内部的一些价值。 对不起我的英语不好。 参考图片以便更好地理解。

预期结果：

我的SQL查询：

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

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

最满意答案

您可以使用CASE表达式计算新的类别值，从而执行所需操作：

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;

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;