locked
Query require in sql RRS feed

  • 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

    Friday, February 7, 2020 12:14 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 7, 2020 1:47 PM

All replies

  • 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.  

    Friday, February 7, 2020 12:27 PM
  • 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>

    Friday, February 7, 2020 1:07 PM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 7, 2020 1:47 PM