R, SQL query in for loop

Question

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 !

add "group by id", this will group by the id. (or you can select with customer table) — Angel Koh
– Angel Koh, Commented Mar 21, 2019 at 11:26
so basically multiple ids have the same user bound to them(eg John is bound to id 1,2,3)? and if so, why? make one ID for one name. I could show you in the answer a query that will sum for each of the ID if you want me to — Andrei Fiordean
– Andrei Fiordean, Commented Mar 21, 2019 at 11:26
I think you don't need a loop here. Instead of looping over each id by its own you could select all ids at once using "...WHERE id IN (", id, ")....". you might have to wrap each single id-number in vector id inside ' ' to make this work. — TinglTanglBob
– TinglTanglBob, Commented Mar 21, 2019 at 11:29
You could select id and value from db then in R join the names by id and do a sum grouped by name? Or are you specifically trying to do this with a query? — Tom Haddow
– Tom Haddow, Commented Mar 21, 2019 at 11:36
the issue is that I have over 100 customers and they have from 1 to even 200 ID so I am not sure how I could one ID for one name @AndreiFiordean — Tmiskiewicz
– Tmiskiewicz, Commented Mar 21, 2019 at 11:36

Tom Haddow · Accepted Answer · 2019-03-21 11:51:37Z

1

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))

answered Mar 21, 2019 at 11:51

Tom Haddow

2301 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tmiskiewicz Over a year ago

I tried to use your solution but I got this error "Error in UseMethod("group_by_") : no applicable method for 'group_by_' applied to an object of class "function""

Mahmoud Abbas · Accepted Answer · 2019-03-21 11:33:41Z

0

This code for summation

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

answered Mar 21, 2019 at 11:33

Mahmoud Abbas

366 bronze badges

Comments

Andrei Fiordean · Accepted Answer · 2019-03-21 11:43:19Z

0

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

answered Mar 21, 2019 at 11:43

Andrei Fiordean

2233 silver badges14 bronze badges

7 Comments

Tom Haddow Over a year ago

From my understanding of his question, it sounds like Table A doesn't exist in his db?

Andrei Fiordean Over a year ago

i think so, it might be possible that table A does not exist in his DB and that is why his job is much harder

Tmiskiewicz Over a year ago

You are right, it doesnt exitst as a db. The type is the list

Andrei Fiordean Over a year ago

your life would be much easier if you would just make another table and link it to your target table, that is my advice for you sir

Tom Haddow Over a year ago

Can you please fix your example of the id list then? or even dput() your customer id list?

|

TinglTanglBob · Accepted Answer · 2019-03-21 12:04:37Z

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)

MKa · Accepted Answer · 2019-03-21 12:11:01Z

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)

Collectives™ on Stack Overflow

R, SQL query in for loop

5 Answers 5

1 Comment

Comments

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related