-1

I have the data like this

enter image description here

I want below result.

enter image description here

1
  • Why do you have two fields with the same name, Quantity?...such a thing is generally not possible except for in an adhoc query. What is your table definitions for this dataset?
    – J.D.
    Commented Dec 9, 2021 at 12:55

2 Answers 2

0

Try this.

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

UNION ALL

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.