Good day all. I have a table called [SPC] with just one column with column name as (Table name) and 20 rows, each row with its table. I am trying to create a for loop to add two new columns: count of rows for each table, count of column for each table.
I get count of rows for each table using:
nrow(sqlQuery(con,F[1]))
[1] 2500011
Here's an explanation of "why" FJCC's code should work.
With this line of code you provided:
SPC_TABLE$Count_of_row= nrow(sqlQuery(con,F[i]))
You're assigning the number of rows in the data.frame returned by sqlQuery to the entireCount_of_row column. So, each iteration of the loop is replacing the result from before. I'll provide some example data.
for (i in 1:nrow(SPC_TABLE)) {
SPC_TABLE$Count_of_row <- nrow(tables[[i]])
SPC_TABLE$Count_of_column <- ncol(tables[[i]])
message("SPC_TABLE for iteration ", i)
print(SPC_TABLE)
}
# SPC_TABLE for iteration 1
# name Count_of_row Count_of_column
# 1 A 3 2
# 2 B 3 2
# 3 C 3 2
# SPC_TABLE for iteration 2
# name Count_of_row Count_of_column
# 1 A 5 3
# 2 B 5 3
# 3 C 5 3
# SPC_TABLE for iteration 3
# name Count_of_row Count_of_column
# 1 A 7 1
# 2 B 7 1
# 3 C 7 1
Like I said, this is what should work. To be sure, you'll need to provide a reprex:
Thank you, this worked. I also want to add percentage of null to SPC using same format but it gives me a constant percentage for eachrow. please what could be the issue.
The only problem I see with your code is that you probably want to divide the result of length() by the product of nrow() and ncol(). As written, you are dividing by nrow() and multiplying by ncol(). Try
thank you very much. this was quite helpful. finally, I want to fetch SPC tables for all DB in the server. lets say they are about 10 dbs,
so I see it to work in this way.
firstly: I select a db(table_list) from the list of dbs, where p= select * from each db.
for(j in 1:nrow(DB_LIST)){TABLE_LIST[j]=sqlQuery(con,p[j])}
secondly:
run SPC_TABLE for each table in the db selected which you helped me out on yesterday.