Joining data with sqldf in R

Question

I have a piece of code:

sql_iv <- "select year, month, day, 
count(HR)
from y2
group by year, month, day
order by year, month, day"

y3=sqldf(sql_iv)

Which calculates how many times a measurement was taken in a single day (amount varies day to day):

     Year Month Day count(HR)
1    2018   4    7    88
2    2018   4    8    327
3    2018   4    9    318
4    2018   4   10    274
5    2018   4   11    345
6    2018   4   12    275
.
.
.
189  2018  10   12    167

Now I need to take these calculated values and join them with my data which has every measurement in a different row (i.e. all the measurements made of April 4th would have to have value 88 in the last column). Could anyone help me out with this?

Data structure for first 10 measurements (out of 48650):

structure(list(Date = structure(c(1523119800, 1523119920, 1523119980, 
1523120280, 1523120340, 1523120400, 1523120460, 1523120520, 1523120580, 
1523120640), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
HR = c("97.0", "98.0", "95.0", "93.0", "94.0", "94.0", "92.0", 
"96.0", "89.0", "90.0"), Year = c(2018, 2018, 2018, 2018, 
2018, 2018, 2018, 2018, 2018, 2018), Month = c(4, 4, 4, 4, 
4, 4, 4, 4, 4, 4), Day = c(7, 7, 7, 7, 7, 7, 7, 7, 7, 7), 
Hour = c(16, 16, 16, 16, 16, 17, 17, 17, 17, 17), Minute = c(50, 
52, 53, 58, 59, 0, 1, 2, 3, 4)), row.names = c(NA, -10L), class = c("tbl_df", 
"tbl", "data.frame"))

Answer 1

are you looking for this?

library(dplyr)
mydata %>% 
  as_tibble() %>%
  left_join(sqldf %>% as_tibble, by = c("Year", "Month", "Day"))