chen jia <[email protected]> wrote: > When I create a new table by using aggregate functions and the GROUP > by clause, the newly generated columns do not have any data type > affinity according to the schema info, while the old columns to which > I apply the aggregate functions all have data type affinity. I wonder > how SQLite determines the data type affinity of columns generated by > aggregate function and the GROUP BY clause.
Haven't you just answered your own question? SQLite doesn't. If this matters to you, don't use CREATE TABLE AS SELECT. Use regular CREATE TABLE to create a table with the desired schema, then INSERT ... SELECT to populate it. > My solution is to use the CREATE TABLE clause to create the new table > and declare the data type affinity explicitly and then INSERT the > values generated by the aggregate functions. This does solve the > problem, but it requires a CREATE TABLE cause every time the aggregate > function and the GROUP by clause is used. Is this the best solution? Yes, if you want control over the schema of your table. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
