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.
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
Member
118 Points
282 Posts
Query require in sql
Feb 07, 2020 12:14 PM|akhterr|LINK
Below is my data
Output
All-Star
53711 Points
24036 Posts
Re: Query require in sql
Feb 07, 2020 12:27 PM|mgebhard|LINK
Use GROUP BY and a LEFT JOIN.
However, your table design duplicate QTY columns and the data does not match the expected results.
Member
118 Points
282 Posts
Re: Query require in sql
Feb 07, 2020 01:07 PM|akhterr|LINK
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>
All-Star
53711 Points
24036 Posts
Re: Query require in sql
Feb 07, 2020 01:47 PM|mgebhard|LINK
The community cannot read your mind. IMHO, the design is poor and you should fix it but below is the SQL.