Remove From My Forums

Union all

Question
0

Sign in to vote
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

0

Sign in to vote

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
0

Sign in to vote

It gives so many rows and there is no clause to retrieve data for pid 211

Wednesday, May 18, 2016 6:14 PM
0

Sign in to vote
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

0

Sign in to vote
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
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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

0

Sign in to vote
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
0

Sign in to vote

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
0

Sign in to vote

How can I send you the sample Data

Thursday, May 19, 2016 7:59 AM
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

Using access with vb

Thursday, May 19, 2016 8:53 AM
0

Sign in to vote
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
- Proposed as answer by Ch. Rajen Singh Saturday, May 28, 2016 1:49 PM
Thursday, May 19, 2016 9:03 AM
0

Sign in to vote

Thank you so much

Thursday, May 19, 2016 9:08 AM

Asked by:

Union all

Question

All replies