sql.R

#install.packages("sqldf")
library(sqldf)

## Loading required package: gsubfn

## Loading required package: proto

## Loading required package: RSQLite

sqldf("select mpg from mtcars")

## Loading required package: tcltk

## Warning: Quoted identifiers should have class SQL, use DBI::SQL() if the
## caller performs the quoting.

##     mpg
## 1  21.0
## 2  21.0
## 3  22.8
## 4  21.4
## 5  18.7
## 6  18.1
## 7  14.3
## 8  24.4
## 9  22.8
## 10 19.2
## 11 17.8
## 12 16.4
## 13 17.3
## 14 15.2
## 15 10.4
## 16 10.4
## 17 14.7
## 18 32.4
## 19 30.4
## 20 33.9
## 21 21.5
## 22 15.5
## 23 15.2
## 24 13.3
## 25 19.2
## 26 27.3
## 27 26.0
## 28 30.4
## 29 15.8
## 30 19.7
## 31 15.0
## 32 21.4

sqldf("select mpg as MILEAGE from mtcars")

##    MILEAGE
## 1     21.0
## 2     21.0
## 3     22.8
## 4     21.4
## 5     18.7
## 6     18.1
## 7     14.3
## 8     24.4
## 9     22.8
## 10    19.2
## 11    17.8
## 12    16.4
## 13    17.3
## 14    15.2
## 15    10.4
## 16    10.4
## 17    14.7
## 18    32.4
## 19    30.4
## 20    33.9
## 21    21.5
## 22    15.5
## 23    15.2
## 24    13.3
## 25    19.2
## 26    27.3
## 27    26.0
## 28    30.4
## 29    15.8
## 30    19.7
## 31    15.0
## 32    21.4

sqldf("select avg(mpg) as AVG_MILEAGE from mtcars")

##   AVG_MILEAGE
## 1    20.09062

sqldf("select avg(mpg) as AVG_MILEAGE from mtcars group by cyl")

##   AVG_MILEAGE
## 1    26.66364
## 2    19.74286
## 3    15.10000

sqldf("select cyl,avg(mpg) as AVG_MILEAGE from mtcars group by cyl")

##   cyl AVG_MILEAGE
## 1   4    26.66364
## 2   6    19.74286
## 3   8    15.10000

sqldf("select cyl,avg(mpg) as AVG_MILEAGE from mtcars group by cyl order by cyl")

##   cyl AVG_MILEAGE
## 1   4    26.66364
## 2   6    19.74286
## 3   8    15.10000

sqldf("select cyl,avg(mpg) as AVG_MILEAGE from mtcars where gear=5 group by cyl order by cyl")

##   cyl AVG_MILEAGE
## 1   4        28.2
## 2   6        19.7
## 3   8        15.4

sqldf("select cyl,avg(mpg) as AVG_MILEAGE,count(cyl) as N from mtcars where gear=5 group by cyl order by cyl")

##   cyl AVG_MILEAGE N
## 1   4        28.2 2
## 2   6        19.7 1
## 3   8        15.4 2