Is there any solution to iterate one MySql query result inside another
loop using ruby.
Using Ruby mysql Gem
In my code fetch_row will not accept any parameters. so my code will
return error.
connection = Mysql.new(@db_host, @db_user, @db_pass, @db_name)
total_make = connection.query("Select make COUNT(cartype) AS cnt
FROM vehicles group by car order by car;")
result = connection.query(“Select make, cartype, COUNT(cartype) AS
cnt FROM vehicles where cartype=‘suv’ group by car order by car;”)
suv = 0
data = “”
i = 0
result.each do |row|
suv += row[1].to_i
total_make.fetch_row(i)
data += row[0] + “\n”
data += "Total Cars: " + total_make[1] + “\n”
data += "Total SUV : " + suv.to_s + “\n”
data += “-----------------------\n”
data = “”
i+=1
end
puts data
MySql Query:
Select make, cartype, COUNT(cartype) AS cnt FROM vehicles where
cartype=‘suv’ group by car order by car;
+-----------+-----------+-----+
| make | cartype | cnt |
+-----------+-----------+-----+
| Ford | suv | 2 |
| Toyota | suv | 3 |
+-----------+-----------+-----+
Select make COUNT(cartype) AS cnt FROM vehicles group by car order
by car;
+-----------+-----+
| make | cnt |
+-----------+-----+
| Ford | 4 |
| Toyota | 5 |
+-----------+-----+
Expected result:
Trying to group cars by make and list total cars, total suv.
Totota
Total Cars: 5
Total SUV : 3
-----------------------
Ford
Total Cars: 4
Total SUV : 2
-----------------------