Your result is giving me total of item and gross wt. I was applying inside my SSRS and cannot sort-by Zone, that's why I want to make a sort by Zone in SQL. I tried to play around with the query you have provided, but my query is going crazy. Actually what I did was I just try to group-by with Zone in those queries,inner and outer. I don't know what function to use inorder to achieve my requirement.

SELECT Zone, ItemCode, [Products], GrossWt,Quantity FROM

( SELECT Zone, ItemCode,ItemName as [Products], GrossWeight AS GrossWt,Quantity FROM PickLists_Details

UNION ALL

SELECT NULL AS Zone, ItemCode, 'Subtotal' AS ItemName, SUM(GrossWeight) AS GrossWt,SUM(Quantity) AS TotalQ

FROM PickLists_Details

GROUP BY ItemCode) source

ORDER BY ItemCode, (CASE WHEN [Products] = 'Subtotal' then 1 else 0 END), [Products]

I just added in one more column to your given script.

The result that I wanna get(to be able to apply well for SSRS) is as follow:

--001/1 xxx1 DEF-3 0.504 1

---null- -null- Subtotal 0.504 1

--001/1 xxx4 YKK-20 3.100 2

---null- -null- Subtotal 3.100 2

--...

--002/1 xxx1 ABC-2 0.756 1

---null- -null- Subtotal 0.756 1

--002/1 xxx2 DEF-3 0.504 1

---null- -null- Subtotal 0.504 1

--etc...

--(if there are two items under xxx1, it will have to be like:

--001/1 xxx1 DEF-3 0.504 1

---null- -null- DEF-4 0.200 2

---null- -null- Subtotal 0.704

I have attached the result image of your first query.