get sum rows based on column values

Question

I have the data like this

I want below result.

Answer 1

Try this.

Select [Commodity], [Business Entities], [Quantity]
from MyTable

UNION ALL

SELECT [Commodity], 'TOTAL', SUM([Quantity])
from MyTable
group by [Commodity]
order by [Commodity]

Answer 2

You can use GROUPING SETS for this

SELECT
  Commodity,
  [Business Entities] = CASE WHEN GROUPING([Business Entities]) = 1 THEN 'Total' ELSE [Business Entities] END,
  Quantity = SUM(Quantity)
FROM YourTabe
GROUP BY GROUPING SETS (
    (Commodity, [Business Entities]),
    (Commodity)
)
ORDER BY
  [Commodity],
  GROUPING([Business Entities]),
  [Business Entities];

The GROUPING function tells us if a column has been grouped or not.