sql query is not giving me desired ouput.

Question

below is query with data,i am trying to sum Received amount ,but it is not getting sum ,

i also want,which pending value is 0 ,then it doesn't get display

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)  
;  
  
SELECT R.inv_ID,  c.Customer_Name,  
 (CASE WHEN  R.Inv_Type = 'Payable' THEN R.item_Weight ELSE 0 END) as Weight,   
 (CASE WHEN  R.Inv_Type = 'Payable' THEN R.Rec_Amount ELSE 0 END) as Payable,  
  (Sum(CASE WHEN R.Inv_Type =  'Received' THEN R.Rec_Amount  ELSE 0 END)) as Received,  
 (CASE WHEN  R.Inv_Type = 'Payable' THEN R.Rec_Amount ELSE 0 END)  -  
    (Sum(CASE WHEN R.Inv_Type =  'Received' THEN R.Rec_Amount  ELSE 0 END)) as Pending  
,  
	'' as txtRec_amt  
  
  
FROM     #tbl_Receivable AS R inner join  
                  #tbl_Customer c   ON R.Customer_ID = C.Customer_ID  
				group by  R.inv_ID,C.Customer_Name  ,R.Inv_Type,R.item_Weight , R.Rec_Amount  

Desire output

Answer 1

It seems that you experienced a problem when item_Weight is only specified in one row of a group. Then try this long sequence too:

drop table if exists #tbl_Receivable, #tbl_Customer

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-11',15000,'Payable',0)

---

;
with E1 as
(
    select
        r.*, 
        cast(Rec_Amount as int) as amount 
    from #tbl_Receivable r
    inner join #tbl_Customer c on c.Customer_ID = r.Customer_ID
),
E2 as
(
    select 
        Inv_ID,
        max(item_Weight) as item_Weight, 
        Customer_ID, 
        min(Rec_Date) as Rec_Date, 
        isnull(sum(case Inv_type when 'Payable' then amount end), 0) as Payable, 
        isnull(sum(case Inv_type when 'Received' then amount end), 0) as Received 
    from E1
    group by Inv_ID, Customer_ID
),
E3 as
(
    select
        Inv_ID as Inv_ID0,
        cast(Inv_ID as varchar(10)) as Inv_ID,
        cast(item_Weight as varchar(10)) as item_Weight,
        cast(Customer_ID as varchar(10)) as Customer_ID,
        cast(Rec_Date as varchar(20)) as Rec_Date,
        Payable,
        Received,
        Payable - Received as Pending
    from E2
), 
E4 as
(
    select *
    from E3
    union all
    select 
        2147483647, 
        '', 
        '',
        '', 
        'TOTAL', 
        sum(Payable), 
        sum(Received), 
        sum(Pending)
    from E3
)
select Inv_ID, item_Weight, Customer_ID, Rec_Date, Payable, Received, Pending
from E4
order by Inv_ID0

If still there are issues, then show the data to reproduce them, and the expected results.

Answer 2

Hi @Analyst_SQL , welcome to Microsoft Q&A forum.

You can try below query:

SELECT a.Inv_ID,item_Weight,b.Customer_ID,min(a.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  

Please let me know if this works or else we can discuss more.

----------

If this helps, please 'Accept Answer' as it could help other community members looking for similar query.

Answer 3

Please try this:

 drop table if exists #tbl_Receivable

 Create table #tbl_Receivable (Rec_ID int,Inv_ID int ,Customer_ID int,Rec_Date date,Rec_Amount INT,Inv_type varchar(50),item_Weight int);

 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)


 ;WITH cte as (
    SELECT 
    Inv_ID, Customer_ID, item_Weight,
    Rec_Date,  
    CASE WHEN Inv_type = 'Payable' THEN Rec_Amount ELSE 0 END as Payable,
    CASE WHEN Inv_type = 'Received' THEN Rec_Amount ELSE 0 END as Received
    FROM #tbl_Receivable
)
SELECT *
    ,SUM(Payable-Received) OVER (PARTITION BY Inv_ID ORDER BY Inv_ID, Customer_ID, Rec_Date) as RemainingBalance
FROM cte
ORDER BY Inv_ID, Customer_ID, Rec_Date

Answer 4

below is query with data, i am trying to SUM Received amount , but it is not getting SUM ,

Most of the work in SQL is done in the DDL. By definition, a table must have a key (you failed ) and the affix "tbl_" is a design flaw called a "tibble"; Google around for some of the funny songs we write about them. Since a table models a set, its name h AS to be a pleural or collective noun. I also see you love to use oversized columns without any thought to the cost and the possibility of bad data. Let's try to fix your DDL first.

> I also want, which pending value is 0 , then it doesn't get display <<

SQL, AS well most modern programming, is based on a tiered architecture. One of the tiers is in charge of displays. We don't do that in the database tier. This is usually covered in freshman programming classes. Let's try to fix what you've got.

First of all, a receivable is a transaction in an accounting system. It is not AS you have done a separate kind of entity. The first week of data modeling you learn all identifiers (_id) are on a nominal scale. By definition, they can't be integers. How can an amount be a string? How can it be a FIFTY character string? In short, you posted garbage.

CREATE TABLE Customers
(customer_id CHAR(5) NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL);

see how this table h AS a key, proper data types and not null constraints. Now it's insert some rows in this.

INSERT INTO Customers
VALUES ('00001', 'Akhter'), ('00002', 'Hussian');

while I've already told you that your receivables table is wrong, let's ignore that and then correct what you did post. Notice that there h AS to be a reference back to the customers table for this to be a relational database. That's what the relation is!

CREAT TABLE Receivables
(rec_id CHAR(3) NOT NULL PRIMARY KEY, --my guess!
inventory_id CHAR(3) NOT NULL,
customer_id CHAR(5) NOT NULL
REFERENCES Customers,
rec_date DATE DEFAULT CURRENT_tIMESTAMP NOT NULL,
rec_amount DECIMAL (8, 2) NOT NULL,
foobar_type CHAR (10) NOT NULL
CHECK (foobar_type IN ('Payable', 'Received')),
item_weight INTEGER NOT NULL
CHECK (item_weight >= 0));

I've made guesses about the correct data types, tried to correct your insertion statement

INSERT INTO Receivables
VALUES
('111', '211', '00001', '2020-03-06', 5000.00, 'Payable', 0),
('112', '211', '00001', '2020-03-07', 2000.00, 'Received', 0),
('113', '222', '00001', '2020-03-08', 8000.00, 'Payable', 0),
('114', '223', '00002', '2020-03-08', 2000.00, 'Payable', 0),
('115', '222', '00001', '2020-03-09', 4000.00, 'Received', 0),
('116', '224', '00001', '2020-03-10', 15000.00, 'Payable', 500),
('117', '211', '00001', '2020-03-12', 1000.00, 'Received', 0) ;

did you want to do a query something like this?

SELECT R.inventory_id, C.customer_name,
SUM (CASE WHEN R.inventory_type = 'Payable' THEN R.item_weight ELSE 0 END) AS payable_weight_tot,
SUM (CASE WHEN R.inventory_type = 'Payable' THEN R.rec_amount ELSE 0.00 END) AS payable_amt_tot,
SUM(CASE WHEN R.inventory_type = 'Received' THEN R.rec_amount ELSE 0.00 END) AS received_amt_tot,
FROM Receivable AS R, Customer AS C
WHERE R.customer_id = C.customer_id
GROUP BY R.inventory_id, C.customer_name;

Answer 5

Hi @Analyst_SQL ,

Please also refer below and check whether it is helpful to you.

;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   

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	0	15000  
			   Total	30000	7000	23000  

Best regards
Melissa

---

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.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table