0
info <- paste("select count (*) as total
                from ANNUAL_CROP a
                where a.CROP_TYPE='Rye' and a.GEO = 'Canada'
                and a.YEAR = '%1968' ")
query <- sqlQuery(conn,info,believeNRows = FALSE)
query

can i ask how can write sqlQuery in R under the condition with sub value, the original dataset has the year variable as a character and i required to query the total of the rows where CROP_TYPE='Rye' and a.GEO = 'Canada' and a.YEAR = 1968

and this the sample of Annual_crops the original dataset

enter image description here

HAJ
  • 11
  • 3
  • If I understand you correctly you want to "translate" your SQL-Querry o R-code, right? – DPH Jan 30 '23 at 14:01
  • For R code to do this, see [summarize by group](https://stackoverflow.com/q/11562656/3358272). My guess (I'm not spending time transcribing your _picture_ of data, please give just images of pics, https://meta.stackoverflow.com/a/285557 (and https://xkcd.com/2116/)), is something like `with(mtcars, tapply(disp, list(cyl, gear), FUN = length))` or `dplyr::count(mtcars, cyl gear)`. – r2evans Jan 30 '23 at 14:04
  • i made connection between R and db2 in cloud , so i required to do some queries on it through R notebook, as i created the cloud db (Annual_crop) through R notebook , so that i want to know how can i query for all rows when the year = 1986 , as in sql for SAS i just write % before the word, but here am a little confusing – HAJ Jan 30 '23 at 16:04
  • 1
    so you need to solve it in db2 where you want to extract the year part from the date which is stored as string (eighter to filter or as a new column) - you can approach this in two ways: work the string like explained here https://stackoverflow.com/questions/68228251/db2-how-to-retrieve-the-last-substring-starting-from-the-end or convert string to date and work from there, for which this should help: https://stackoverflow.com/questions/4852139/converting-a-string-to-a-date-in-db2 – DPH Jan 30 '23 at 16:27
  • I don't know how it looks in the database, but I would _hope_ that the `YEAR` date-like object in the db2 would be stored as a date-like object, not as a character string. Many databases (I cannot say how many) store/display the year _first_, which suggests that your hack to use `'%1968'` should instead be `'1968%'` (I don't know for certain, I don't use db2, but SQL itself is clear here). Another likely culprit is that in most DBs I use, `%` is only useful with the `LIKE` operator, not `=`. So perhaps one of `a.YEAR LIKE '%1968'` or `a.YEAR LIKE '1968%'` (depending on my previous point). – r2evans Feb 03 '23 at 13:56

0 Answers0