question

SaeedSansaj avatar image
SaeedSansaj asked

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
union
10 |1200

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

David Wimbush avatar image
David Wimbush answered
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
10 |1200

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

Great Appreciate your advice and thanks for your help Saeed
0 Likes 0 ·
SaeedSansaj avatar image
SaeedSansaj answered
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
10 |1200

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 ·
SaeedSansaj avatar image
SaeedSansaj answered
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
10 |1200

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

David Wimbush avatar image
David Wimbush answered
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.
10 |1200

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

SaeedSansaj avatar image
SaeedSansaj answered
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
10 |1200

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

David Wimbush avatar image
David Wimbush answered
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
10 |1200

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

SaeedSansaj avatar image
SaeedSansaj answered
It gives the following error message "Circular reference caused by alias 'qty'in query definition's SELECT list"
1 comment
10 |1200

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 ·
SaeedSansaj avatar image
SaeedSansaj answered
Sorry for the troubles I had given you. It is Microsoft Access Microsoft.Jet.OLEDB.4.0
1 comment
10 |1200

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 ·
SaeedSansaj avatar image
SaeedSansaj answered
Please try to find a way to work I am forwarding the db for your perusal could you please look into it [link text][1] [1]: /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
10 |1200

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 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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