locked
MySQL query about union RRS feed

  • 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

Answers

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


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Proposed as answer by Patrick Hurst Friday, April 15, 2016 5:17 PM
    • Marked as answer by Eric__Zhang 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 Eric__Zhang 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;


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Proposed as answer by Patrick Hurst Friday, April 15, 2016 5:17 PM
    • Marked as answer by Eric__Zhang 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 Eric__Zhang 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