add new column using SQLite

I am trying to insert a new column in a R dataframe usi开发者_如何学Gong sqldf, using the example 4 from sqldf

abbr <- data.frame (species = levels(iris[,"Species"]),
                    abbr = c("s","ve","vi"))
sqldf("select abbr, avg(Sepal_Length) from iris natural join abbr group by species")

sqldf("select abbr, avg(Sepal_Length) from iris join abbr using(Species) group by Species")

Both sqldf command works, but it fails when I change the column name of abbr from abbr to abbr_col, I don't know which abbr in the sql syntax should be changed.

Thanks.

update #01

> abbr <- data.frame (species = levels(iris[,"Species"]),
+                     abbr_col = c("s","ve","vi"))
> sqldf("select abbr_col, avg(Sepal_Length) from iris natural join abbr_col group by species")
Error in sqliteExecStatement(con, statement, bind.data) : 
  RS-DBI driver: (error in statement: no such table: abbr_col)

I tried renaming all abbr into abbr_col, but fail.

One “abbr” is table name, another one is column name:

abbr_table <- data.frame (species = levels(iris[,"Species"]),
                          abbr_col = c("s","ve","vi"))
sqldf("select abbr_col, avg(Sepal_Length) from iris natural join abbr_table group by species")

If you want to rename column from abbr to abbr_col, then you need change all of the abbr occurrences to abbr_col.