SQL Syntax in R programming

Question

I have the below data set.

QTR MONTHS  SALES
Q1  JAN     100
Q1  FEB     200
Q2  MAY     300
Q2  JUN     400

I want to see something like this

Row Labels  JAN FEB MAY JUN Grand Total
Q1          100 200         300
Q2                  300 400 700
Grand Total 100 200 300 400 1000

I want to write an SQL query in R.

Do you want to using SQL in R? Or do you want to get that output. You don't need to do use SQL in R to get that output... — Dason, Sep 26 '17 at 18:47
Hi dason, I can get it by using Summarise. But I would like to switch to SQL completely :). — avinash talari, Sep 26 '17 at 18:49
Now we know what you want to achieve. I can't tell whether this makes sense, but clearly your post does not contain a _question_. Furthermore, you didn't include any hints on how you want to solve problem. StackOverflow works best if the questioners do a little research on their own and can include that research in their questions... — ventiseis, Sep 26 '17 at 18:50
`mout <- reshape(mdat, idvar = "QTR", timevar = "MONTHS", direction = "wide"); mout$total <- rowSums(mout[,-1], na.rm = T)` — M--, Sep 26 '17 at 18:51
https://stackoverflow.com/questions/31164350/dplyr-summarize-with-subtotals — M--, Sep 26 '17 at 18:54
There really isn't a very good way to do this in SQL that I'm aware of. Some variants of SQL have an `unpivot` command that makes this kind of transformation only moderately painful. This is a situation where, unless you're really determined to keep the operations in the database, it's probably easier to do it in R. — Benjamin, Sep 26 '17 at 19:00

score 0 · Answer 1 · answered Sep 26 '17 at 18:49

0

you can use a library called sqldf to do sql in R.

library(sqldf)

data(titanic3, package="PASWR")

df <- sqldf(`select age from titanic3 where age != "NA"`)

head(df)

answered Sep 26 '17 at 18:49

Mike Tung

1 Answers1