CODEWITHSUNDEEP
mysqldatabase
nIx..
nIx..

Reputation: 370

Create table as select from clause

I have table T1 and it has say 3 text columns: a, b, c. Now I want to create table T2 out of T1 using following query

create table T2 as 
   select
    a,b, sum(c) as sum_col 
   from T1 
    where 'some where condition here'

Now column sum_col is created with datatype double, where as I want it to be created as decimal(20,7). Can anybody suggest is there a way to do it?

Upvotes: 0

Views: 646

Answers (3)

Ritu Dadhich
Ritu Dadhich

Reputation: 1

Does it work in MS SQl ?

create table T2 as select a,b, sum(c) as sum_col from T1 where 'some where condition here'

Upvotes: 0

Ravinder Reddy
Ravinder Reddy

Reputation: 23992

You can use cast function to define new data type for the derived column.

create table T2 as
 select a,b, cast( sum(c) as decimal(20,7) ) as sum_col
 from T1
  where 'some condition here'

MySQL Fiddle: Demo

Inline demo at MySQL command prompt: 

mysql> create table t1( i int );
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

mysql> insert into t1 values( 6 ), ( 9 );
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table t2 as
    ->  select cast( sum(i) as decimal(20,7) ) as sum_total
    ->  from t1;
Query OK, 1 row affected (0.45 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc t2;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| sum_total | decimal(20,7) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> select * from t2;
+------------+
| sum_total  |
+------------+
| 15.0000000 |
+------------+
1 row in set (0.00 sec)

Upvotes: 3

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

Give this a try. This examples set colum a and b to datatype int

create table T2 (a int, b int, sum_col decimal(20,7))        
select a,b, sum(c) as sum_col from T1 where

To try the sum

create table T2 (a int, b int, sum_col decimal(20,7)) 
select '1' a, '1' b, sum(1*3) as sum_col;

Upvotes: 0

Related Questions