SQL combine two tables into one table and add a new column

Question

I need to combine two tables into one. Ans also, add a column (assign an int value) to the new table on SQL. So that the rows from table1 and ones from table2 are assigned with different values.

Example,

   table1
   ID1  ID2  ID3 VALUE

   table2
   ID1  ID2  ID3  VALUE

   table3
   ID1  ID2  ID3  VALUE

i need to combine table3 and table2 into a new table and add a new column

    table_new
    top_id  ID2  ID3 VALUE

It is Netezza SQL.

   INSERT INTO new_table
   SELECT *
   FROM 
   (
       SELECT * ,  **sum (table1.VALUE * table2.VALUE) AS new_value**
       FROM  table1 
       JOIN 
            table2 
       ON  table1.id1 = table2.id1
       GROUP BY table2.id2,  table2.id3    
   ) AS tt_a  # here, I need to add a new column to tt, call it as top_id and also assign an int 
              # value to it, such as 80
   UNION ALL
   SELECT *
   FROM 
   (
       SELECT * ,  **sum (table1.VALUE * table3.VALUE) AS new_value**
       FROM  table1 
       JOIN 
            table3 
       ON  table1.id1 = table3.id1
       GROUP BY table3.id2,  table3.id3   
   ) AS tt_b   # here, I need to add a new column to tt, call it as top_id and also assign an  
               # int value to it, such as 81
    **ORDER BY top_id**

I got error:

I em new to SQL.

  ERROR [HY000] ERROR:  0 : Functionality not implemented

Any help would be appreciated.

Try harder. (to explain what you are trying to do) – wvdz May 08 '14 at 18:55 — wvdz, May 08 '14 at 18:55

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

0

It's not entirely clear what you want, but I'm going to assume it's a UNION ALL example.

UNION ALL will combine the result sets of 2 or more SELECT statements. It returns all rows from each select statement (even if the row exists in more than one of the SELECT statements) as 1 result set.

example:

select '81' as top_id,id2,id3 from T1 group by id2,id3
UNION ALL
select '79' as top_id,id3,id400 from T1 group by id3, id400
UNION ALL
SELECT '80' as top_id,id2,id3
   FROM  table1 
   JOIN 
        table2 
   ON  table1.id1 = table2.id1
   GROUP BY table1.id2,  table2.id3  
;

Each select statement (this example combines 3) should be a valid query all by itself and should return the same amount of columns with the same datatypes.

reference: http://www.techonthenet.com/sql/union_all.php

edited Jun 20 '20 at 09:12

Community

1
1

answered May 08 '14 at 19:37

r.m

374
1
5

please see my update, I need to do some calculations and also insert the combines two tables into one ! – user3448011 May 08 '14 at 20:08
your example contains multiple parts using **. * is a reserved symbol in SQL and can't be used like that and could be the reason. to avoid any confusion, please post the exact query you used when you got that error. – r.m May 09 '14 at 23:18

SQL combine two tables into one table and add a new column

1 Answers1