0

Why does this SELECT statment return a float value? How can I cast it into an integer?

select area.name, coalesce(((avg(recording.length)*count(recording.length))/1000/60),0)
fubar
  • 16,918
  • 4
  • 37
  • 43
JimBelushi2
  • 285
  • 1
  • 3
  • 18

1 Answers1

1

If the result of avg(recording.length)*count(recording.length) isn't a multiple of 60000, you'll get a fraction when you do that division. You can use ROUND() to round this to the nearest integer.

Also, since the average is the sum divided by the count, multiplying by the count just returns the sum. You can just use that directly.

SELECT area.name, coalesce(ROUND(SUM(recording.length)/1000/60)), 0)
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Can you even get a NULL from a SUM? Not sure that COALESCE does anything here. – LukStorms Jun 20 '18 at 22:58
  • @LukStorms He didn't show the whole query, but I assume there'a a `LEFT JOIN` with `recording`, it will return `NULL` if there are no matching rows. – Barmar Jun 20 '18 at 23:01
  • Thx. And I found a [post](https://stackoverflow.com/questions/17048343/) about SUM returning NULL's and why. So it's possible in MySql, I just wasn't aware of it yet. – LukStorms Jun 20 '18 at 23:14