CODEWITHSUNDEEP
mysqlsqlrfor-loop
Tmiskiewicz
Tmiskiewicz

Reputation: 403

R, SQL query in for loop

I have a list that contains customers and their ID. It looks like the one below:

customers_id <- list(x = John(1,2,3), Rick = c(4), Sam = c(5,6))

and a database that looks like the one below and calls 'db'

date        id   value
2017-05-12  1      51 
2017-05-13  2      3  
2017-05-14  3      217
2017-05-15  1      12
2017-05-16  2      98
2017-05-17  3      123
2017-05-18  1      78
2017-05-19  2      36
2017-05-20  4      178
2017-05-18  5      728
2017-05-19  6      336
2017-05-20  4      718
2017-05-18  5      758
2017-05-19  6      366
2017-05-20  4      787

I tried to make a for loop but couldn't figuer out the right solution. I think that in the loop should be a query that will take a correct ID and sum values for ID

corect_values <- paste(" SELECT date, id, SUM(value) FROM db WHERE id = '", id, "' ")

So I have two issues: How to put a query into for loop and how to write a sql query that will take into account all id for customer.

Result should like the one below:

John  618
Rick  1683
Sam   2188

Do you have any idea how it can be solved ? Thanks for any help !

Upvotes: 0

Views: 2046

Answers (5)

MKa
MKa

Reputation: 2318

If you really need to use for loop to only run the query for the specific customers then you can do:

for (i in 1:length(customers_id)) {

  sql_q <- paste0("SELECT '", names(customers_id)[i], "' AS customer, sum(value) AS value FROM db WHERE id IN (", paste(customers_id[[i]], collapse = ", "), ")")

}

sql_q returns

[1] "SELECT 'John' AS customer, sum(value) AS value FROM db WHERE id IN (1, 2, 3)"
[1] "SELECT 'Rick' AS customer, sum(value) AS value FROM db WHERE id IN (4)"
[1] "SELECT 'Sam' AS customer, sum(value) AS value FROM db WHERE id IN (5, 6)"

Then just append the data to get your result, e.g.

# Example Data given
customers_id <- list(John = c(1,2,3), Rick = c(4), Sam = c(5,6))

# Assuming you have your db connection setup in 'con'
sql_list <- list()
for (i in 1:length(customers_id)) {

  sql_q <- paste0("SELECT '", names(customers_id)[i], "' AS customer, sum(value) AS value FROM db WHERE id IN (", paste(customers_id[[i]], collapse = ", "), ")")
  sql_d <- sqlQuery(con, sql_q)
  sql_list[[i]] <- sql_d

}

combined_d <- do.call("rbind", sql_list)

Upvotes: 0

TinglTanglBob
TinglTanglBob

Reputation: 647

Does this select deliver the desired results?

customer_id = sample(c(1:6), 10, replace = T) # just some dummy-user ids as an example

customer_id = paste0("'", customer_id, "'")
customer_id = paste(customer_id, collapse =  ", ")

corect_values = paste0("SELECT sum(value) FROM db where id IN (", customer_id, ") GROUP BY id")

ofc it doesn't since you do not want to group by id but by name and name does cover more than just one id.

maybe this can solve the problem. The drawback is: You still have to make a request for every name. So even if the following code works, i think it would still be better to create a reference table in your db to join name and id as @Andrei Fiordean has suggested in the comments.

select_this <- function(temp_ids)
{

  temp_ids = paste0("'", temp_ids, "'")
  temp_ids = paste(temp_ids, collapse =  ", ")
  corect_values = paste0("SELECT sum(value) FROM db where id IN (", temp_ids, ")")
  # results = request select from db here
  return(results)
}

customers_id <- list("John" = c(1,2,3), "Rick" = c(4), "Sam" = c(5,6))
sapply(customers_id, select_this)

Upvotes: 0

Tom Haddow
Tom Haddow

Reputation: 230

Assuming you have a data.frame of customer names and id's like so...

customers_id <- data.frame(Names = c("John", "John", "John", "Rick", "Sam", "Sam"),
                           id = c(1:6))

Query all id and value from db... lets say this is now assigned to df

SELECT id, value FROM db

Now you can left_join on your names, group by this and sum the value.

library(dplyr)

df <- left_join (df, customers_id, by = "id")

result <- df %>% 
  group_by(Names) %>% 
  summarise(value = sum(value))

Upvotes: 1

Andrei Fiordean
Andrei Fiordean

Reputation: 223

I will try to describe as best as i can: when you have 2 tables for example:(lets take your tables)

Table_A) ID, user_name

Table_B) ID, date, user_id(matched to the table above as a foreign key), value

you can do a query like this:

SELECT Table_A.user_name, sum(Table_B.value)
FROM Table_A join Table_B on Table_a.ID = Table_B.user_id
GROUP BY Table_A.ID

This query will sum all values for all users that you have. It will only show one entry for each of the user with all of the values summed up

Upvotes: 0

Mahmoud Abbas
Mahmoud Abbas

Reputation: 36

This code for summation

SELECT SUM(values) total FROM db WHERE customers_id = id;

Upvotes: 0

Related Questions