Display all item from tbl_Bottles.

Question

Display all item from tbl_Bottles.

Analyst_SQL 3,576

I want to display all item from tbl_Bottles ,below i tried but not getting expected result.

 Create table #tbl_bottles (B_ID int,B_Name varchar(50))  
                               
       Create table #tbl_GRN (G_ID int,B_ID int,G_Qty int)  
       Create table #tbl_Issue (I_ID int,B_ID int,I_Qty int)  
  
  
	                              
       insert into #tbl_bottles VALUES(1,'19Liter')  
       insert into #tbl_bottles VALUES(2,'6Liter')  
       insert into #tbl_bottles VALUES(3,'2Liter')  
       insert into #tbl_bottles VALUES(4,'Nozzel')  
       insert into #tbl_bottles VALUES(5,'Cap')  
	          insert into #tbl_bottles VALUES(6,'Stand')  
       insert into #tbl_bottles VALUES(7,'Pump')  
	          insert into #tbl_bottles VALUES(8,'Pip')  
       insert into #tbl_bottles VALUES(9,'Blue Stand')  
  
	          insert into #tbl_GRN VALUES(1,1,5)  
       insert into #tbl_GRN VALUES(2,1 ,8)  
	    insert into #tbl_GRN VALUES(3,3 ,12)  
		  insert into #tbl_GRN VALUES(4,5 ,6)  
	            insert into #tbl_Issue VALUES(1,1,5)  
				 insert into #tbl_Issue VALUES(1,1,2)  
       insert into #tbl_Issue VALUES(2,3 ,8)  
  
  
	   ;With Cte1 as (  
Select B_ID as Code,B_Name as Item from #tbl_bottles  
), cte2  
as  
(  
select B_ID as Code ,Sum(isnull(G_QTY,0)) as GQty   from #tbl_GRN   
group by B_ID  
)  
,cte3 as   
(select B_ID as Code ,Sum(isnull(I_QTY,0)) as IQty   from #tbl_issue  
group by B_ID)  
  
select Item , isnull(GQty-IQty,0)Qty  from Cte1     join cte2 on cte1.Code=cte2.Code   
  
full   join cte3 on Cte2.Code=cte3.Code

0 comments

Answer accepted by question author

EchoLiu-MSFT 14,626

Hi @Analyst_SQL ,

Please try:

;with cte as  
(select tb.B_ID,sum(isnull(tg.G_Qty,0)) G_Qty   
 from #tbl_bottles  tb  
 left join #tbl_GRN tg on tb.B_ID=tg.B_ID  
 group by tb.B_ID)  
,cte2 as  
(select tb.B_ID,sum(isnull(ti.I_Qty,0)) I_Qty   
 from #tbl_bottles  tb  
 left join #tbl_Issue ti on tb.B_ID=ti.B_ID  
 group by tb.B_ID)  
,cte3 as  
(select t.B_Name Item,c.G_Qty,c2.I_Qty,isnull((c.G_Qty-c2.I_Qty),0) balance   
 from cte c  
 join cte2 c2 on c.B_ID=c2.B_ID  
 join #tbl_bottles t on c.B_ID=t.B_ID)  
  
select * from cte3  
union all  
select 'Total', sum(G_Qty),sum(I_Qty),sum(balance)  
from  cte3

Output:

    Item G_Qty I_Qty balance  
    19Liter 13 7 6  
    6Liter 0 0 0  
    2Liter 12 8 4  
    Nozzel 0 0 0  
    Cap 6 0 6  
    Stand 0 0 0  
    Pump 0 0 0  
    Pip 0 0 0  
    Blue Stand 0 0 0  
    Total 31 15 16

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.

0 comments

2 additional answers

Your answer

Answer 1

Try this:

;WITH CTE1 AS (
    SELECT b.B_ID, SUM(g.G_Qty) AS G_Qty
    FROM #tbl_bottles AS b
    LEFT JOIN #tbl_GRN AS g ON g.B_ID = b.B_ID
    GROUP BY b.B_ID
),
CTE2 AS (
    SELECT b.B_ID, SUM(i.I_Qty) AS I_Qty
    FROM #tbl_bottles AS b
    LEFT JOIN #tbl_Issue AS i ON i.B_ID = b.B_ID
    GROUP BY b.B_ID
),
CTE3 AS (
    SELECT 
        'Total' AS Item, 
        G_Qty = (SELECT SUM(G_Qty) FROM #tbl_GRN),
        I_Qty = (SELECT SUM(I_Qty) FROM #tbl_Issue)
)

SELECT 
    b.B_Name AS Item, 
    ISNULL(c1.G_Qty, 0) AS G_Qty, 
    ISNULL(c2.I_Qty, 0) AS I_Qty,
    ISNULL(c1.G_Qty, 0) - ISNULL(c2.I_Qty, 0) AS Balance
FROM #tbl_bottles AS b
LEFT JOIN CTE1 AS c1 ON c1.B_ID = b.B_ID
LEFT JOIN CTE2 AS c2 ON c2.B_ID = b.B_ID

UNION ALL

SELECT 
    'Total' AS Item, 
    G_Qty,
    I_Qty, 
    G_Qty - I_Qty AS Balance
FROM CTE3;

Answer 2

Nasreen Akter 10,896 Volunteer Moderator

Hi @Analyst_SQL ,

would you please try the following statement. Thanks!

select Item , isnull(GQty,0) as GQty, isnull(IQty,0) as IQty, (isnull(GQty,0)-isnull(IQty,0)) as Balance    
from Cte1      
left join cte2 on cte1.Code=cte2.Code   
left join cte3 on Cte2.Code=cte3.Code

0 comments

Share via

Display all item from tbl_Bottles.

2 additional answers

Your answer