Query require in sql[Answered] RSS

• • Reply
  akhterr

  Member

  93 Points

  226 Posts

  Query require in sql

  Feb 07, 2020 12:14 PM|akhterr|LINK

  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

• • Reply
  mgebhard

  All-Star

  49131 Points

  21745 Posts

  Re: Query require in sql

  Feb 07, 2020 12:27 PM|mgebhard|LINK

  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.  

• • Reply
  akhterr

  Member

  93 Points

  226 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>

• • Reply
  mgebhard

  All-Star

  49131 Points

  21745 Posts

  Re: Query require in sql

  Feb 07, 2020 01:47 PM|mgebhard|LINK

  akhterr

  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