Hi there, 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.
I read I read the link http://www.sqlite.org/datatype3.html. One paragraph says "When grouping values with the GROUP BY clause values with different storage classes are considered distinct, except for INTEGER and REAL values which are considered equal if they are numerically equal. No affinities are applied to any values as the result of a GROUP by clause." If I understand it correctly, the columns created by aggregate functions with a GROUP by clause do not have any expected data types. Is this correct? 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? I need to solve this problem because I often extract data from SQLite into other statistics software, and the data type affinity help these softwares determine what type of data is extracted. Furthermore, it helps SQLite to have well-defined data type affinity as well. The following are some examples: I create the new table ya_vol by using: create table ya_vol as select PERMNO, year, variance(ya) as pya_var, stdev(ya) as pya_sd, count(*) as nya, variance(ya_ebi) as pya_var_ebi, stdev(ya_ebi) as pya_sd_ebi, variance(ya_ebit) as pya_var_ebit, stdev(ya_ebit) as pya_sd_ebit, variance(ya_ebitda) as pya_var_ebitda, stdev(ya_ebitda) as pya_sd_ebitda from past_ya where ya is not null group by PERMNO, year order by PERMNO, year The schema info of ya_vol is: sqlite> .schema ya_vol CREATE TABLE ya_vol( PERMNO INT, year INT, pya_var, pya_sd, nya, pya_var_ebi, pya_sd_ebi, pya_var_ebit, pya_sd_ebit, pya_var_ebitda, pya_sd_ebitda ); CREATE INDEX ya_vol_permno_year_idx on ya_vol (permno,year); The schema info of the old table past_ya is: sqlite> .schema past_ya CREATE TABLE past_ya( PERMNO INT, year INT, beg_year INT, end_year INT, lag_year INT, ya REAL, ya_ebi REAL, ya_ebitda REAL, ya_ebit REAL ); Best, Jia -- 700 Fisher Hall 2100 Neil Ave. Columbus, Ohio 43210 http://www.fisher.osu.edu/~chen_1002/ _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
