Total is coming in First row,which must display in last Row.

Analyst_SQL 3,551 Reputation points
2021-01-26T04:21:40.31+00:00

Hi @MelissaMa-MSFT
When i am using Order by Trans_ID , then total is coming in First row ,which should not come.

 Create table #tbl_COA (Level_Four_ID int,Level_Four_Name varchar(50),Opening decimal(10,2))  
       
 Create table #tbl_trans_type (Trans_Type_ID int,trans_type_name varchar(50))  
 Create table #tbl_transection (Trans_ID int,Level_Four_ID_C int,Level_Four_ID_D int,Trans_Amount Decimal(10,2),Trans_date date,Trans_Type_ID int)  
 INSERT INTO #tbl_COA VALUES(1231,'Abdul Rauf',60000)  
 INSERT INTO #tbl_COA VALUES(1222,'Cheque In Hand',45000)  
 INSERT INTO #tbl_COA VALUES(1215,'MBL 833968',0)  
       
      
 insert into #tbl_trans_type VALUES(1,'Online')  
 insert into #tbl_trans_type VALUES(2,'Cheque')  
 insert into #tbl_trans_type VALUES(3,'Deposite')  
 insert into #tbl_trans_type VALUES(4,'Tranfer')  
 insert into #tbl_trans_type VALUES(4,'Return')  
      
 INSERT INTO #tbl_transection VALUES(1,1231,1222,55000,'2021-01-18',2)  
 INSERT INTO #tbl_transection VALUES(2,1231,1222,55000,'2021-01-18',2)  
 INSERT INTO #tbl_transection VALUES(3,1222,1215,44444,'2021-01-18',3)   
 INSERT INTO #tbl_transection VALUES(4,1215,1222,44444,'2021-01-18',5)    
      
      
 ;WITH cte  
 as(   
 SELECT NULL Trans_ID,NULL Trans_Type,NULL TransDate,'Opening' Trans_Remarks,NULL Code,NULL Head,null Debit,null Credit,isnull(opening,0) Balance   
 from #tbl_COA   
 where Level_Four_ID=1222  
 UNION ALL  
 SELECT T.Trans_ID,ty.trans_type_name as Trans_Type,Convert(varchar, T.Trans_Date ,101)as TransDate,  
 (CONCAT(COA.Level_Four_Name ,' ','Online Receipt C/O',' ',COAc.Level_Four_Name,' ', 'Rs. ',T.Trans_Amount))   as Trans_Remarks,   
 T.Level_Four_ID_C as Code  ,COAc.Level_Four_Name as Head, T.Trans_Amount as Debit, CAST(0 AS decimal(18,2)) as Credit,T.Trans_Amount- CAST(0 AS decimal(18,2)) Balance   
 FROM #tbl_transection T  
 inner  join #tbl_COA COA on COA.Level_Four_ID=T.Level_Four_ID_D  
 inner join #tbl_COA COAc on COAc.Level_Four_ID=T.Level_Four_ID_C  
 inner join #tbl_trans_type ty  on ty.trans_type_ID=T.Trans_Type_ID  
 where COA.Level_Four_ID=1222    
 UNION ALL  
 SELECT T.Trans_ID,ty.trans_type_name as Trans_Type, Convert(varchar, T.Trans_Date ,101)as TransDate,  
 (CONCAT(COA.Level_Four_Name ,'Online Receipt C/O',' ',COA.Level_Four_Name, '',T.Trans_Amount))   as Trans_Remarks,  
 T.Level_Four_ID_D as Code,COA.Level_Four_Name as Head, CAST(0 AS decimal(18,2)) as Debit, Trans_Amount as Credit,CAST(0 AS decimal(18,2))-Trans_Amount Balance   
 FROM #tbl_transection T  
 inner join #tbl_COA COA on COA.Level_Four_ID=T.Level_Four_ID_D  
 inner join #tbl_trans_type ty  on ty.trans_type_ID=T.Trans_Type_ID  
 where T.Level_Four_ID_c=1222  
 ),cte1 as (  
 SELECT Trans_ID,Trans_Type,TransDate,Trans_Remarks, Code,Head,Debit,Credit,sum(Balance) over (order by (select null) ROWS UNBOUNDED PRECEDING) Balance  
 FROM cte)  
 select * from cte1   
 UNION ALL  
 SELECT  NULL ,NULL ,NULL , null,null, 'Total',sum(Debit),sum(Credit),null  
 from cte1 order by Trans_ID  

Ouput
60429-balance.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,914 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,466 Reputation points
    2021-01-26T05:27:47.623+00:00

    Beginning with line 44 [ ), cte1 as ( ] change the code to

          ),cte1 as (
          SELECT Trans_ID,Trans_Type,TransDate,Trans_Remarks, Code,Head,Debit,Credit,sum(Balance) over (order by (select null) ROWS UNBOUNDED PRECEDING) Balance
          FROM cte),
          cte2 As (
          select Trans_ID,Trans_Type,TransDate,Trans_Remarks, Code,Head,Debit,Credit,Balance 
          from cte1 
          UNION ALL
          SELECT  NULL ,NULL ,NULL , null,null, 'Total',sum(Debit),sum(Credit),null
          from cte1) 
          select Trans_ID,Trans_Type,TransDate,Trans_Remarks, Code,Head,Debit,Credit,Balance
          from cte2
          order by Case When Head = 'Total' Then 1 Else 0 End, Trans_ID
    

    Tom

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-01-26T05:48:37.153+00:00

    Hi @Analyst_SQL

    When you use Order by Trans_ID, it will be sorted in descending order of Trans_ID by default, which is the result in the screenshot you posted.

      ;WITH cte  
      as(   
      SELECT NULL Trans_ID,NULL Trans_Type,NULL TransDate,'Opening' Trans_Remarks,NULL Code,NULL Head,null Debit,null Credit,isnull(opening,0) Balance   
      from #tbl_COA   
      where Level_Four_ID=1222  
      UNION ALL  
      SELECT T.Trans_ID,ty.trans_type_name as Trans_Type,Convert(varchar, T.Trans_Date ,101)as TransDate,  
      (CONCAT(COA.Level_Four_Name ,' ','Online Receipt C/O',' ',COAc.Level_Four_Name,' ', 'Rs. ',T.Trans_Amount))   as Trans_Remarks,   
      T.Level_Four_ID_C as Code  ,COAc.Level_Four_Name as Head, T.Trans_Amount as Debit, CAST(0 AS decimal(18,2)) as Credit,T.Trans_Amount- CAST(0 AS decimal(18,2)) Balance   
      FROM #tbl_transection T  
      inner  join #tbl_COA COA on COA.Level_Four_ID=T.Level_Four_ID_D  
      inner join #tbl_COA COAc on COAc.Level_Four_ID=T.Level_Four_ID_C  
      inner join #tbl_trans_type ty  on ty.trans_type_ID=T.Trans_Type_ID  
      where COA.Level_Four_ID=1222    
      UNION ALL  
      SELECT T.Trans_ID,ty.trans_type_name as Trans_Type, Convert(varchar, T.Trans_Date ,101)as TransDate,  
      (CONCAT(COA.Level_Four_Name ,'Online Receipt C/O',' ',COA.Level_Four_Name, '',T.Trans_Amount))   as Trans_Remarks,  
      T.Level_Four_ID_D as Code,COA.Level_Four_Name as Head, CAST(0 AS decimal(18,2)) as Debit, Trans_Amount as Credit,CAST(0 AS decimal(18,2))-Trans_Amount Balance   
      FROM #tbl_transection T  
      inner join #tbl_COA COA on COA.Level_Four_ID=T.Level_Four_ID_D  
      inner join #tbl_trans_type ty  on ty.trans_type_ID=T.Trans_Type_ID  
      where T.Level_Four_ID_c=1222  
      ),cte1 as (  
      SELECT Trans_ID,Trans_Type,TransDate,Trans_Remarks, Code,Head,Debit,Credit,sum(Balance) over (order by (select null) ROWS UNBOUNDED PRECEDING) Balance  
      FROM cte)  
      select * from cte1   
      UNION ALL  
      SELECT  NULL ,NULL ,NULL , null,null, 'Total',sum(Debit),sum(Credit),null  
      from cte1 order by Head,Trans_ID  
    

    Output:
    60523-image.png

    order by Head,Trans_ID  
    

    First sort by Head positive order, then sort by Trans_ID positive order.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    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.

    1 person found this answer helpful.
    0 comments No comments