You must Sign In to post a response.
  • Category: SQL Server

    Dispaly data using sql tables

    Hi,

    I have 2 tables issue and Receive, tables data is:

    Receive table data:
    quality | Receive_Takas
    A | 5
    B | 5
    A | 5
    B | 5
    C | 5
    A | 6
    C | 6
    B | 6
    D | 5
    D | 5


    Issue table data:
    quality | Issue_Takas
    A | 6
    C | 6
    B | 6
    A | 1
    D | 2
    A | 1

    I want the remaining takas, remaining takas = sum(Receive_Takas) - sum(Issue_Takas)

    I need to data like below:


    Quality | Remaining_Takas
    A | 8
    B | 10
    C | 5
    D | 8


    Can anyone give me the sql query
  • #752297
    Use 'GROUP BY' clause to group the records with same id, use SUM function to sum of columns
    see below snippet

    SELECT
    (SELECT SUM(Receive_Takas) FROM Receive GROUP BY quality)
    -
    (SELECT SUM(Issue_Takas) FROM Issue GROUP BY quality)
    AS Remaining_Takas


    hope it helps

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #752300
    Hi,

    Use below sample

    select ( sum(receive_Takas)-sum(Issue_Takas)) as remaining_Takas
    from receive r,
    Issue I
    where r.Quality=I.Quality

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #752301
    Refer the following SQL Script;

    CREATE TABLE [Receive]
    (
    Quality VARCHAR(2),
    Receive_takas INT
    )

    CREATE TABLE [Issue]
    (
    Quality VARCHAR(2),
    Issue_takas INT
    )

    INSERT INTO [Receive]
    SELECT 'A',
    5
    UNION ALL
    SELECT 'B',
    5
    UNION ALL
    SELECT 'A',
    5
    UNION ALL
    SELECT 'B',
    5
    UNION ALL
    SELECT 'C',
    5
    UNION ALL
    SELECT 'A',
    6
    UNION ALL
    SELECT 'C',
    6
    UNION ALL
    SELECT 'B',
    6
    UNION ALL
    SELECT 'D',
    5
    UNION ALL
    SELECT 'D',
    5

    INSERT INTO [Issue]
    SELECT 'A',
    6
    UNION ALL
    SELECT 'C',
    6
    UNION ALL
    SELECT 'B',
    6
    UNION ALL
    SELECT 'A',
    1
    UNION ALL
    SELECT 'D',
    2
    UNION ALL
    SELECT 'A',
    1

    SELECT DISTINCT r1.Quality,
    r2.Receive_takas - r3.Issue_takas
    FROM [Receive] r1
    CROSS apply (SELECT Quality,
    Sum(Receive_takas) AS Receive_Takas
    FROM [Receive]
    WHERE Quality = r1.Quality
    GROUP BY Quality)r2
    CROSS apply (SELECT Quality,
    Sum(Issue_takas) AS Issue_Takas
    FROM [Issue]
    WHERE Quality = r1.Quality
    GROUP BY Quality)r3

  • #752311
    Hai Sampath,
    I don't understand that why you need 3 tables to store the values.You can have only 1 table which will do all that.
    You can have a single table which will have the below columns:

    quality | Receive_Takas | Issue_Takas | Remaining_Takas

    Now for the fist time, when the takas is received, it will insert in to Receive_Takas. Next time when there is issue, it will just update the table and then update the remaining column values.
    So when you want to get any data, it will be easy to retrieve from this table.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #752998
    HI SAMPATH,
    USE THIS BELOW SCRIPT

    SELECT QUALITY,SUM(RECEIVE_TAKAS) AS RECEIVE_TAKAS,SUM(ISSUE_TAKAS) AS ISSUE_TAKAS,
    SUM(RECEIVE_TAKAS-ISSUE_TAKAS) AS REMAINING_TAKAS FROM
    (SELECT QUALITY,SUM(RECEIVE_TAKAS) AS RECEIVE_TAKAS,0 AS ISSUE_TAKAS FROM RECEIVE GROUP BY QUALITY
    UNION
    SELECT QUALITY,0 AS RECEIVE_TAKAS,SUM(ISSUE_TAKAS) AS ISSUE_TAKAS FROM ISSUE GROUP BY QUALITY)REMAINING
    GROUP BY QUALITY

    I HOPE HELP THIS SCRIPT

    DHARMENDRA


  • Sign In to post your comments