I have a statement similar to this:
CREATE TABLE table1 AS SELECT agent_id, SUM(face_amount) AS sum_face, SUM(fees) AS sum_fees FROM transactions GROUP BY agent_id;
transactions.face_amount and transactions.fees have precision 2 places to the right of the decimal. But when I do my CREATE TABLE, it rounds the sums to integers! If I do my SELECT without the CREATE TABLE, I get sums with 2 decimal places.
How do I get it to put those 2 decimal places into the table? I have tried tricks such as
CREATE TABLE table1 AS SELECT agent_id, SUM(face_amount * 1.00) AS sum_face,
and
CREATE TABLE table1 AS SELECT agent_id, SUM(face_amount + 0.00) AS sum_face,
but still I am left with only integers. Is there any way to get the decimal precision into the table without having to first define the table, and then use INSERT?
Start Free Trial
