# question

## UNION ALL

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 211 200 Table 2 pid qty 210 10 211 5 212 2 211 3 I wanted to get the grand total of Pid 211 from these two tables (Grand Total 208) I am coding the same as follows: Select Pid, Sum(Table1.qty) 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

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
You don't need the where clause in the first part. It doesn't work like that. And the group by is a separate clause so you can't include it in the where clause. It gets a bit tricky when you combine so many things at once. Try building it up one layer at a time. This will combine all the rows from the two tables into one list: select Pid , qty from Table1 union all select pid , qty from Table2 Then you can enclose that in an outer query to do the sum and apply the where clause: select Pid , sum(qty) as qty from ( select Pid , qty from Table1 union all select pid , qty from Table2 ) x where Pid = 211 group by pid
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Likes 0 ·
I also wanted to deduct some figures (Salqty) in the same table 1 Can I write like this Select pid, sum(qty-salqty) as qty How can I add one more where clause which requires to filter data for the year=2016 Can I code like this where pid=211 and year=2016 Thanks in advance for the help
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes, those should both work fine.
0 Likes 0 ·
Thanks your quick response My coding is as follows select Table1.Pid, sum(Table1.qty-Table1.Salqty) as qty from ( select Pid, qty, salqty from Table1 union all select Table2.pid, Table2.qty from Table2 ) x where Pid = 211 and yr=2016 group by pid But it returns null Appreciate your help

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'm surprised that works at all. If you take out the instances of 'Table1.' in the first two lines that should sort it out.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The following code works fine select Pid, sum(qty) as qty from (select Pid, qty from Table1 union all select pid, Adjqty from Table2) x where Pid = 211 group by pid But when a clause is added as follows select Pid, sum(qty) as qty from (select Pid, qty from Table1 union all select Pid, Adjqty from Table2) x where Pid = 211 and yr=2016 group by pid it asks for the parameter value for yr select Pid, sum(qty-salqty) as qty from (select Pid, qty from Table1 union all select pid, adjqty from Table2) x where Pid = 211 and yr=2016 group by pid it does not work at all Note Salqty is in Table1 and yr is in Table2 hope it is clear

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

How about this then?: select sum(qty) as qty from ( select qty - salqty as qty from Table1 where Pid = 211 union all select qty from Table2 where Pid = 211 and yr = 2016 ) x

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It gives the following error message "Circular reference caused by alias 'qty'in query definition's SELECT list"
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It works in SQL Server. What database engine are you using?
0 Likes 0 ·
Sorry for the troubles I had given you. It is Microsoft Access Microsoft.Jet.OLEDB.4.0
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sorry. In that case, I don't know what will work.
0 Likes 0 ·
Please try to find a way to work I am forwarding the db for your perusal could you please look into it [link text] : /storage/temp/3427-data2016.mdb Here in the Query 'Union all', the result is correct (61+5) but it is displayed in two rows instead of 66 in one row in the Query "NewSql", the result is wrong but the data is displayed in a row

data2016.mdb (312.0 KiB)
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sorry Saeed but I'm a bit out of date on Access. Last time I used it was over 20 years ago and it came on floppy disks!
0 Likes 0 · 