• ### Question

• hi

table A
=======
Name  |  Qty
------------
AA    |  1
BB    |  2
CC    |  3
DD    |  4

table B
=======
Name  |  Qty
------------
AA    |  0
BB    |  0
EE    |  0
ZZ    |  0

and i need this as resoult

Name  |  Qty
------------
AA    |  1
BB    |  2
CC    |  3
DD    |  4
EE    |  0
ZZ    |  0

how to do it ? i try with union but not get the resault
Friday, April 15, 2016 4:37 PM

• Why do you want the result? Why do you select the value return? I am going to assume that you simply want a union of these queries and then a sum of the qty across both tables:

```with cte as (
SELECT Name, Qty
from [Table A]
union all
SELECT Name, Qty
from [Table B]
)
select Name, SUM(QTY) As QTY
from cte
GROUP BY NAME;```

• Proposed as answer by Friday, April 15, 2016 5:17 PM
• Marked as answer by Sunday, April 24, 2016 10:12 AM
Friday, April 15, 2016 5:01 PM
• ```create table tableA (name varchar(30), Qty int)

Insert into tableA values('AA',1),('BB',2),('CC',3),('DD',4)
create table tableB (name varchar(30), Qty int)
Insert into tableB values('AA',0),('BB',0),('EE',0),('ZZ',0)

Select Coalesce(a.name,b.name) as name, Coalesce(a.Qty,b.Qty) as Qty from tablea a full outer join tableb b on a.name=b.name
--or
Select Coalesce(a.name,b.name) as name,  Coalesce(a.Qty,0)+Coalesce(b.Qty,0) as Qty
from tablea a full outer join tableb b on a.name=b.name

drop table tablea,tableb
```

• Marked as answer by Sunday, April 24, 2016 10:12 AM
Friday, April 15, 2016 6:11 PM

### All replies

• Why do you want the result? Why do you select the value return? I am going to assume that you simply want a union of these queries and then a sum of the qty across both tables:

```with cte as (
SELECT Name, Qty
from [Table A]
union all
SELECT Name, Qty
from [Table B]
)
select Name, SUM(QTY) As QTY
from cte
GROUP BY NAME;```

• Proposed as answer by Friday, April 15, 2016 5:17 PM
• Marked as answer by Sunday, April 24, 2016 10:12 AM
Friday, April 15, 2016 5:01 PM
• ```create table tableA (name varchar(30), Qty int)

Insert into tableA values('AA',1),('BB',2),('CC',3),('DD',4)
create table tableB (name varchar(30), Qty int)
Insert into tableB values('AA',0),('BB',0),('EE',0),('ZZ',0)

Select Coalesce(a.name,b.name) as name, Coalesce(a.Qty,b.Qty) as Qty from tablea a full outer join tableb b on a.name=b.name
--or
Select Coalesce(a.name,b.name) as name,  Coalesce(a.Qty,0)+Coalesce(b.Qty,0) as Qty
from tablea a full outer join tableb b on a.name=b.name

drop table tablea,tableb
```

• Marked as answer by Sunday, April 24, 2016 10:12 AM
Friday, April 15, 2016 6:11 PM
• You specifically mentioned MySQL - this is a forum for SQL Server.  Different vendors support different SQL dialects; any suggestions you get here may not work in MySQL.

Friday, April 15, 2016 7:44 PM