Share via

sql query is not giving me desired ouput.

Analyst_SQL 3,551 Reputation points
2020-12-03T03:39:02.663+00:00

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

44613-pay.png

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,802 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,637 questions
0 comments
Accepted answer
  1. Viorel 117.3K Reputation points
    2020-12-04T13:27:49.703+00:00

    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.

    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anurag Sharma 17,606 Reputation points
    2020-12-03T04:34:24.393+00:00

    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.

    1 person found this answer helpful.
    0 comments
  2. Tom Phillips 17,731 Reputation points
    2020-12-04T15:46:50.327+00:00

    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
    1 person found this answer helpful.
    0 comments
  3. Joe Celko 16 Reputation points
    2020-12-04T19:46:39.327+00:00

    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;

    1 person found this answer helpful.
    0 comments
  4. MelissaMa-MSFT 24,196 Reputation points
    2020-12-03T06:10:12.257+00:00

    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

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.