Query require in sql

Question

User-367318540 posted

Below is my data 

CREATE TABLE #tbl_Group(B_ID INT,B_Name VARCHAR(50))
CREATE TABLE #tbl_BloodDonor(D_ID INT,D_Name VARCHAR(50),B_ID int,D_QTY int)
CREATE TABLE #tbl_Cross(C_ID INT,D_ID int,C_QTY int,B_ID int )  
       
INSERT INTO #tbl_Group VALUES(1,'A')   
INSERT INTO #tbl_Group VALUES(2,'B') 
INSERT INTO #tbl_Group VALUES(3,'C')
INSERT INTO #tbl_Group VALUES(4,'D')  
   
INSERT INTO #tbl_BloodDonor VALUES(101,'Hussain',1,1)  
INSERT INTO #tbl_BloodDonor VALUES(102,'Akhter',2,1) 
INSERT INTO #tbl_BloodDonor VALUES(103,'Ali',3,1) 
INSERT INTO #tbl_BloodDonor VALUES(104,'Rehan',2,1) 

INsert into #tbl_Cross Values(1,101,1,1)
INsert into #tbl_Cross Values(2,102,1,2)

Output

Group	Stock
A	0
B	1
C	1
D	0

Answer 1

User475983607 posted

Use GROUP BY and a LEFT JOIN.

SELECT g.B_Name AS [Group], COALESCE(SUM(c.C_QTY), 0) AS [Stock]
FROM #tbl_Group AS g
	LEFT JOIN #tbl_BloodDonor AS b ON g.B_ID = b.B_ID
	LEFT JOIN #tbl_Cross AS c on c.B_ID = g.B_ID
GROUP BY g.B_Name

However, your table design duplicate QTY columns and the data does not match the expected results.  

Answer 2

User-367318540 posted

Hi mgebhard

your table design duplicate QTY columns  what you means? i ave to minus from cross table qty from blood donor qty

and your provided query result is not my expected result

your table design duplicate QTY columns

<div>Group Stock</div> <div>A          1</div> <div>B           2</div> <div>C           0</div> <div>D           0</div>

Answer 3

User475983607 posted

Hi mgebhard

your table design duplicate QTY columns  what you means? i ave to minus from cross table qty from blood donor qty

and your provided query result is not my expected result

your table design duplicate QTY columns

<div>Group Stock</div> <div>A          1</div> <div>B           2</div> <div>C           0</div> <div>D           0</div>

The community cannot read your mind.  IMHO, the design is poor and you should fix it but below is the SQL.

SELECT t.B_Name, t.[Sum] - COALESCE(SUM(c.C_QTY), 0)
FROM(
SELECT g.B_ID, g.B_Name,  COALESCE(SUM(b.D_QTY), 0) AS [Sum]
FROM #tbl_Group AS g
	LEFT JOIN #tbl_BloodDonor AS b ON g.B_ID = b.B_ID
GROUP BY g.B_ID, g.B_Name
) as t
LEFT JOIN #tbl_Cross AS c ON t.B_ID = c.B_ID
GROUP BY t.B_Name, t.[Sum]
ORDER BY t.B_Name