how to realize the following SQL command in R? select distinct A, B, count(C) from TABLE group by A, B ; quit; Best Regards --------------------------------- Sponsored Link Get a free Motorola Razr! Today Only! Choose Cingular, Sprint, Verizon, Alltel, or T-Mobile. [[alternative HTML version deleted]]
Xiaodong Jin wrote:> how to realize the following SQL command in R? > > select distinct A, B, count(C) > from TABLE > group by A, B > ; > quit; > > Best RegardsYou don't say which database you are using, but I have found RODBC to be very effective. library("RODBC") Have a look at odbcConnect(), and sqlQuery(). It is quite straightforward; even I managed to use it! Otherwise try help.search("sql") which provides links to many other, database-specific packages. Mark -- Dr. Mark Wardle Clinical research fellow and Specialist Registrar in Neurology, C2-B2 link, Cardiff University, Heath Park, CARDIFF, CF14 4XN. UK
Jin, Xiaodong Jin <close2ceo <at> yahoo.com> writes:> how to realize the following SQL command in R? > > select distinct A, B, count(C) > from TABLE > group by A, B > ; > quit; >if you want to perform such "query" on a data.frame! then table(table$a, table$b) will produce similar output, but in 2-way table, or this for similar output table(paste(table$a, "-", table$b)) Gregor
Xiaodong Jin wrote:> I just need to query ordinary 3-column excel data e.g. > V1 V2 V3 > I1 C1 1 > I1 C1 1 > ... << DATA snipped >>Hi. It is better to keep discussion on list, rather than emailing individuals. You need to read http://cran.r-project.org/doc/manuals/R-data.html#Reading-Excel-spreadsheets RODBC allows you to run arbitrary queries against Excel spreadsheets. I don't use Excel (well, try not to), so I can't vouch for it, but it should work. Best wishes, Mark -- Dr. Mark Wardle Clinical research fellow and Specialist Registrar in Neurology, C2-B2 link, Cardiff University, Heath Park, CARDIFF, CF14 4XN. UK
Xiaodong Jin <close2ceo at yahoo.com> writes:> how to realize the following SQL command in R? > > select distinct A, B, count(C) > from TABLE > group by A, B > ; > quit;The functional equivalent of is statement in R on a dataframe is: TABLE$ones <- 1; aggregate(TABLE$ones, list(TABLE$a, TABLE$b), sum); HTH, Jens
sqlQuery() function in RODBC library will be helpful. Sincerely, On Nov 15, 2006, at 1:33 AM, Jens Scheidtmann wrote:> Xiaodong Jin <close2ceo at yahoo.com> writes: > >> how to realize the following SQL command in R? >> >> select distinct A, B, count(C) >> from TABLE >> group by A, B >> ; >> quit; > > The functional equivalent of is statement in R on a dataframe is: > > TABLE$ones <- 1; > aggregate(TABLE$ones, list(TABLE$a, TABLE$b), sum); > > HTH, > > Jens > > ______________________________________________ > R-help at stat.math.ethz.ch mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting- > guide.html > and provide commented, minimal, self-contained, reproducible code. > > >==========================================Dong H. Oh Ph. D Candidate Techno-Economics and Policy Program College of Engineering, Seoul National University, Seoul, 151-050, Republic of Korea E-mail:doriaba2 at snu.ac.kr Mobile: +82-10-6877-2109 Office : +82-2-880-9142 Fax: +82-2-880-8389