# Union all

• ### 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 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 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,

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:

FROM PurchaseDetail, Medmast
WHERE (((PurchaseDetail.PCODE)=[Medmast].[Pcode]) AND ((PurchaseDetail.POSTED)=False) AND ((Medmast.TITLE)="PROLOMET  XL 25 TAB"))
GROUP BY Medmast.TITLE;
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
INNER JOIN MedMast ON Adjustments.Pcode = MedMast.PCODE
)
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:

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

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