Click here to Skip to main content
15,071,205 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Below is my data ,i am trying to display or Sum Received amount in one row,but it is not displaying data in one row,means inv_Id 224 is repeating two times,which should not be.

SQL
Create table #tbl_Receivable (Rec_ID int,Inv_ID int ,Customer_ID int,Rec_Date date,Rec_Amount varchar(50),Inv_type varchar(50),item_Weight int);
  Create table #tbl_Customer ( Customer_ID int ,Customer_Name varchar(50));
    
  insert into #tbl_Customer values (1,'Akhter'),(2,'Hussian');
    
   INSERT INTO #tbl_Receivable VALUES
            
   (111,211,1,'2020-03-06',5000,'Payable',0),
   (112,211,1,'2020-03-07',2000,'Received',0),
   (113,222,1,'2020-03-08',8000,'Payable',0),
   (114,223,2,'2020-03-08',2000,'Payable',0),
    (115,222,1,'2020-03-09',4000,'Received',0),
       (116,224,1,'2020-03-10',15000,'Payable',500),
          (117,211,1,'2020-03-12',1000,'Received',0),
		     (118,224,1,'2020-03-15',1000,'Received',0)
 ;


What I have tried:

SQL
;with cte as (
 SELECT a.Inv_ID,item_Weight,b.Customer_ID,min(a.Rec_Date) Rec_Date
  ,SUM((CASE WHEN a.INV_TYPE= 'Payable' THEN CAST(a.Rec_Amount as int)  ELSE 0 END)) as Payable, 
  SUM((CASE WHEN a.INV_TYPE= 'Received' THEN CAST(a.Rec_Amount as int)  ELSE 0 END)) as Received, 
  SUM((CASE WHEN a.INV_TYPE= 'Payable' THEN CAST(a.Rec_Amount as int)  ELSE 0 END)) - 
  SUM((CASE WHEN a.INV_TYPE= 'Received' THEN CAST(a.Rec_Amount as int)  ELSE 0 END)) as Pending
  from  #tbl_Receivable a
  inner join #tbl_Customer b on a.Customer_ID = b.Customer_ID
  GROUP BY a.INV_ID,b.Customer_Name,a.item_Weight,b.Customer_ID)
    
  select cast(Inv_ID as varchar(100)) Inv_ID
 , cast(item_Weight as varchar(100)) item_Weight
 , cast(Customer_ID as varchar(100)) Customer_ID
 , cast(Rec_Date as varchar(100)) Rec_Date
 ,Payable,Received,Pending
 from cte 
  union all
  select '','','','Total',sum(Payable),sum(Received),sum(pending)
  from cte 


Ouput which is coming.

rec — ImgBB[^]
https://ibb.co/HCPbh5N
Posted
Updated 4-Dec-20 2:47am
v3

1 solution

The two rows for Inv_ID 224 have different Item_Weight values.

Your GROUP BY clause include the Item_Weight column.

Therefore, those two rows cannot be grouped together into one.

It's not clear how you want to combine the rows, but you'll need to remove the irrelevant columns from your GROUP BY clause.

Try something like this:
SQL
WITH cte As
 (
    SELECT
        Inv_ID,
        SUM(Item_Weight) As Item_Weight,
        Min(Customer_ID) As Customer_ID,
        Min(Rec_Date) As Rec_Date,
        SUM(CASE INV_TYPE WHEN 'Payable' THEN CAST(Rec_Amount As int) ELSE 0 END) As Payable,
        SUM(CASE INV_TYPE WHEN 'Received' THEN CAST(Rec_Amount As int) ELSE 0 END) As Received
    FROM
        #tbl_Receivable
    GROUP BY
        Inv_ID
 )
 SELECT
    R.Inv_ID,
    R.Item_Weight,
    R.Customer_ID,
    R.Rec_Date,
    R.Payable,
    R.Received,
    R.Payable - R.Received As Pending
 FROM
    cte As R

UNION ALL

SELECT
    Null,
    Null,
    Null,
    Null,
    Sum(Payable),
    Sum(Received),
    Sum(Payable) - Sum(Received)
FROM
    cte As R
;
   
Comments
akhter86 4-Dec-20 9:01am
   
i do not want to repeat inv_ID,as your provided query ,is repeating Inv_Id
Richard Deeming 4-Dec-20 9:02am
   
You obviously haven't run the query I provided, which does NOT repeat the Inv_ID column. 🤦‍♂️
akhter86 4-Dec-20 11:46am
   
thanks ....
Richard Deeming 4-Dec-20 9:12am
   
Demo[^]

Output:
Inv_ID  | Item_Weight | Customer_ID | Rec_Date   | Payable | Received | Pending
-------------------------------------------------------------------------------
211     |           0 |           1 | 2020-03-06 |    5000 |     3000 |    2000
222     |           0 |           1 | 2020-03-08 |    8000 |     4000 |    4000
223     |           0 |           2 | 2020-03-08 |    2000 |        0 |    2000
224     |         500 |           1 | 2020-03-10 |   15000 |     1000 |   14000
(null)  |      (null) |      (null) |     (null) |   30000 |     8000 |   22000

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900