Johnathon22
Johnathon22

Reputation: 341

Returning SQL as decimal

I use Dashing as the project with Ruby.

I have a SQL query that returns 19.42 when run in the database. However, when I put it into code it returns .1942. What I am trying to figure out is how to get the decimal point to move right by two places.

Here is what the query and code look like:

require 'mysql2'

SCHEDULER.every '2h', :first_in => 0 do |job|

  # MySQL connection
  db = Mysql2::Client.new(:host => "host", :username => "username", :password => "password", :port => port, :database => "database" )

  sql6 = "SELECT AVG(tickets.total_user_waiting) / (60 * 60) AS 'Total Tickets' FROM tickets WHERE tickets.status IN ('resolved', 'archived') AND tickets.date_resolved between date_format(date_sub(now(), interval 7 day), '%Y-%m-%d 00:00:00') and date_format(date_sub(now(), interval 1 day), '%Y-%m-%d 23:59:59');"
  response_sql = db.query(sql6)

send_event('response_sql',    {current: response_sql.first['Total Tickets']})

Here is the result of the SQL query alone from the database:

+-------------+
| 19.40648160 |
+-------------+

The output of the code that is written in Ruby looks like:

0.194064816E2

I'm not so much worried about removing the end numbers as I am just getting the decimal two places to the right, although that would be ideal.

I've tried select round but had no luck with that in the database.

Upvotes: 0

Views: 189

Answers (2)

Johnathon22
Johnathon22

Reputation: 341

The solution that fixed the issue I was having is.

send_event('response_sql',    {current: response_sql.first['Total Tickets'].ceil})

So what I did was added .ceil to the end of the send event. It then rounded it to just two numbers.

Upvotes: -1

StuffAndThings
StuffAndThings

Reputation: 341

The value 0.194064816E2 is mathmatically equivelent to 19.40648160. It's as if you wrote 0.194064816 x 10^2. For instance, if I fire up IRB and throw that value into a variable, the proper float returns:

2.0.0-p481 :001 > x = 0.194064816E2
 => 19.4064816 

I'm not sure why Ruby is printing that particular notation, but if you're performing any sort of additional operations, all of the numbers should work out properly.

Upvotes: 3

Related Questions