How Do I ORDER Result Sets for each Column using ASC/DESC on GROUP BY GROUPING SETS

Hi @abiodunajai

1) How do I sort the ItemNo in ASCENDING order of 1-10 ?

You should not use ORDER BY NEWID() in the ROW_NUMBER function, cause NEWID() returns random numbers.
You need to change it to ROW_NUMBER() OVER (ORDER BY SUM(a.PercentageCover) DESC) AS ItemNo

2) Since ItemNo 10(now 4) shall be the Total, I don't need a Row Number and the PolicyRefCode, how do I stop the ROW numbering and the PolicyRefCode on the 9th row so that numbers 10(now 4) and 0000002311 will not be added to the Last Row.

Try this solution:

;WITH CTE AS  
(  
 SELECT ROW_NUMBER()OVER(ORDER BY SUM(a.PercentageCover) DESC) AS ItemNo  
       ,a.PolicyRefCode  
       ,d.ShortDescription AS PolicyName  
       ,a.DebitNoteNo AS DebitNoteNo  
       ,b.Name As InsurerName  
       ,a.GrossAmount AS GrossAmount  
       ,SUM(a.PercentageCover) AS PercentageCover  
       ,a.InsuerLeadCode AS InsuerLeadCode  
       ,SUM(a.PremiumAmount) AS PremiumAmount  
       ,SUM(a.NAICOMLevyAmount) AS NAICOMLevyAmount  
       ,SUM(a.OverridenCommissionAmount) AS OverridenCommissionAmount  
       ,SUM(a.TotalCommission) AS TotalCommission  
       ,SUM(a.NetPremium) AS NetPremium    
 FROM Underwriting_InsurerPolicyDistribution As a   
      INNER JOIN Underwriting_Insurer_Branch As b ON a.InsBranchCode = b.InsBranchCode  
      INNER JOIN Underwriting_Policy_Code As d ON a.PolicyCode = d.PolicyCode    
 WHERE a.DebitNoteNo='DN0000003042'  
 GROUP BY a.PolicyRefCode,a.PercentageCover,a.DebitNoteNo,a.InsuerLeadCode,b.Name,a.GrossAmount,d.ShortDescription  
)  
SELECT COALESCE(CAST(ItemNo AS VARCHAR(20)),'')AS ItemNo  
      ,COALESCE(PolicyRefCode,'')AS PolicyRefCode  
      ,COALESCE(PolicyName,'')AS PolicyName  
      ,COALESCE(DebitNoteNo,'')AS DebitNoteNo  
      ,COALESCE(InsurerName,'TOTAL')AS InsurerName  
      ,COALESCE(GrossAmount,'0')AS GrossAmount  
      ,SUM(PercentageCover)AS PercentageCover  
      ,COALESCE(CAST(InsuerLeadCode AS VARCHAR(20)),'')AS InsuerLeadCode  
      ,SUM(PremiumAmount)AS PremiumAmount  
      ,SUM(NAICOMLevyAmount)AS NAICOMLevyAmount  
      ,SUM(OverridenCommissionAmount)AS OverridenCommissionAmount  
      ,SUM(TotalCommission)AS TotalCommission  
      ,SUM(NetPremium)AS NetPremium  
FROM CTE  
GROUP BY GROUPING SETS((ItemNo,PolicyRefCode,PolicyName,DebitNoteNo,InsurerName,GrossAmount,InsuerLeadCode),())  

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.