locked
Union all RRS feed

  • Question

  • Can anybody help me in solving this problem
    I have two tables. I wanted to add the values in two columns and to get a grand total.
    e.g

        Table 1
        pid      qty     salqty
        211    200      50

       

         Table 2
        pid     qty   yr
        210   10   2016
        211    5    2016
        212    2    2016
        211    3    2015

    I wanted to get the grand total of Pid 211 for the year 2016 from these two tables (Grand Total 155)

     I am coding the same as follows:

        Select Pid, Sum(Table1.qty-Salqty)
        from Table1
        where Table1.pid=table2.pid
        and Group by Pid
        UNION ALL
        Select Table2.pid, sum(Table2.qty)
        from Table2
        Group by Table2.pid


    Appreciate help

    • Moved by Eric__Zhang Sunday, May 29, 2016 2:18 PM off topic
    Wednesday, May 18, 2016 5:09 PM

All replies

  • select  A.pid,A.qty+B.qty-A.salQty
    from Table1 A Inner Join Table B on A.pid=B.Pid
    where b.yr=2016


    Hope it Helps!!

    Wednesday, May 18, 2016 5:24 PM
  • Can anybody help me in solving this problem
    I have two tables. I wanted to add the values in two columns and to get a grand total.
    e.g

        Table 1
        pid      qty     salqty
        211    200      50

       

         Table 2
        pid     qty   yr
        210   10   2016
        211    5    2016
        212    2    2016
        211    3    2015

    I wanted to get the grand total of Pid 211 for the year 2016 from these two tables (Grand Total 155)

     I am coding the same as follows:

        Select Pid, Sum(Table1.qty-Salqty)
        from Table1
        where Table1.pid=table2.pid  and yr=2016
        and Group by Pid
        UNION ALL
        Select Table2.pid, sum(Table2.qty)
        from Table2

        Group by Table2.pid


    Appreciate help


    Wednesday, May 18, 2016 5:26 PM
  • It gives so many rows and there is no clause to retrieve data for pid 211

    Wednesday, May 18, 2016 6:14 PM
  • It gives so many rows and there is no clause to retrieve data for pid 211

    select  A.pid,A.qty+B.qty-A.salQty
    from Table1 A Inner Join Table B on A.pid=B.Pid
    where b.yr=2016 and A.pid=211



    Hope it Helps!!

    Wednesday, May 18, 2016 7:55 PM
  • Hi Sansaj,

    To get the expected output, you can reference below sample.

    CREATE TABLE TABLE1(	pid INT	,qty INT	,salqty INT	)
    
    INSERT INTO TABLE1
    VALUES (	211	,200	,50	);
    
    CREATE TABLE TABLE2(	pid INT	,qty INT	,yr INT	);
    
    INSERT INTO TABLE2
    VALUES (	210	,10	,2016	)
    	,(	211	,5	,2016	)
    	,(	212	,2	,2016	)
    	,(	211	,3	,2015	);
    
    ;WITH Cte(pid,qty) AS
    (
    SELECT pid,qty-salqty FROM TABLE1 WHERE pid=211
    UNION ALL
    SELECT pid, qty FROM TABLE2 WHERE pid=211 AND yr=2016
    )
    SELECT pid,SUM(qty) totalQty FROM Cte
    GROUP BY pid

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support

    Thursday, May 19, 2016 2:16 AM
  • No, it doesn't work.

    Do you have any other suggestion?


    • Edited by Sansaj Thursday, May 19, 2016 7:07 AM
    Thursday, May 19, 2016 7:07 AM
  • Hi Sansaj,

    When you say "it doesn't work", please be more specific. Have you got any error? or the output is not expected? If not, what is the expected?


    Eric Zhang
    TechNet Community Support

    Thursday, May 19, 2016 7:10 AM
  • These two tables have hundreds of records.  I have shown here only a sample

    If you can give me your email id I can mail you the db

    Thursday, May 19, 2016 7:11 AM
  • Hi Sansaj,

    Of course we provide advice on your sample. If that sample is not your case, then please post a more specific sample.

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support

    Thursday, May 19, 2016 7:15 AM
  • Thank you very much for your support

    More specifically, the following are the code which I wrote.  It works but it gives the data in two rows.  What I need is the total of these two rows in a row

    Here I have 3 tables.  One table is the master where it shows the title of Pcode

    Quote:

    SELECT Medmast.TITLE, Sum(Purchasedetail.qty+Purchasedetail.Retqty+Purchasedetail.Adjqty+Purchasedetail.FreeQty+Purchasedetail.ReplaceQty-Purchasedetail.Salqty) AS StockQty
    FROM PurchaseDetail, Medmast
    WHERE (((PurchaseDetail.PCODE)=[Medmast].[Pcode]) AND ((PurchaseDetail.POSTED)=False) AND ((Medmast.TITLE)="PROLOMET  XL 25 TAB"))
    GROUP BY Medmast.TITLE;
    UNION ALL SELECT MedMast.TITLE, Sum(Adjustments.Adjqty) AS Aqty
    FROM Adjustments INNER JOIN MedMast ON Adjustments.Pcode = MedMast.PCODE
    WHERE (Adjustments.nYear)=13
    group by title;

    Unquote

    Hope it is clear

    Thursday, May 19, 2016 7:42 AM
  • Hi Sansaj,

    Please try below sample, not tested as there's no sample data.

    ;WITH Cte(TITLE,qty) AS
    (
    SELECT Medmast.TITLE
    	,Purchasedetail.qty + Purchasedetail.Retqty + Purchasedetail.Adjqty + Purchasedetail.FreeQty + Purchasedetail.ReplaceQty - Purchasedetail.Salqty AS StockQty
    FROM PurchaseDetail
    	,Medmast
    WHERE (
    		((PurchaseDetail.PCODE) = [Medmast].[Pcode])
    		AND ((PurchaseDetail.POSTED) = False)
    		AND ((Medmast.TITLE) = "PROLOMET  XL 25 TAB")
    		)
    UNION ALL
    SELECT MedMast.TITLE
    	,Adjustments.Adjqty AS Aqty
    FROM Adjustments
    INNER JOIN MedMast ON Adjustments.Pcode = MedMast.PCODE
    WHERE (Adjustments.nYear) = 13
    )
    SELECT TITLE,SUM(qty) totalQty FROM Cte
    GROUP BY TITLE


    Eric Zhang
    TechNet Community Support

    Thursday, May 19, 2016 7:49 AM
  • I have also done the same in another way.  Here I am getting the answer in a row.  But the total figure is wrong.

    It calculates the qty in table purchasedetail twice (61+61)

    and there are two records in the table adjustments (3+2)

    The actual result should be 61+5

    Coding is as follows

    Quote:

    SELECT Sum(Purchasedetail.qty+Purchasedetail.Retqty+Purchasedetail.Adjqty+Purchasedetail.FreeQty+Purchasedetail.ReplaceQty-Purchasedetail.Salqty) AS StockQty, MedMast.TITLE AS Description, Sum(Adjustments.Adjqty) AS SumOfAdjqty
    FROM Adjustments INNER JOIN (MedMast INNER JOIN Purchasedetail ON MedMast.PCODE = Purchasedetail.PCODE) ON Adjustments.Pcode = MedMast.PCODE
    WHERE (((Purchasedetail.PCODE)=[Medmast].[Pcode] And (Purchasedetail.PCODE)=13399) AND ((Purchasedetail.POSTED)=False) AND ((Adjustments.nYear)=13))
    GROUP BY MedMast.TITLE;

    Unquote

    Sorry for the trouble


    • Edited by Sansaj Thursday, May 19, 2016 7:55 AM
    Thursday, May 19, 2016 7:51 AM
  • Hi Sansaj,

    Well, what is the database version? Please send the database to my personal mail and be more specific on the output.

    Do remember to mask sensitive data before sending.


    Eric Zhang
    TechNet Community Support

    Thursday, May 19, 2016 7:58 AM
  • How can I send you the sample Data
    Thursday, May 19, 2016 7:59 AM
  • thanks your prompt response.

    Please let me have your personal email Id so that I can mail you the data

    Thursday, May 19, 2016 8:12 AM
  • Hi Sansaj,

    I've received a mdb file as an attachment, that seems an Access file, are you using SQL Server or Access?



    Eric Zhang
    TechNet Community Support

    Thursday, May 19, 2016 8:45 AM
  • Using access with vb
    Thursday, May 19, 2016 8:53 AM
  • Hi Sansaj,

    Then I'm afraid you've come to the wrong forum. Though SQL Server and Access are both databases, the Query can be used in SQL Server may probably not available in Access.

    I'd suggest you post your question in the Access forum. Thanks for your understanding.


    Eric Zhang
    TechNet Community Support

    Thursday, May 19, 2016 9:03 AM
  • Thank you so much
    Thursday, May 19, 2016 9:08 AM