SQL: Calculate bitrate

Question

I have a column with bytes, and another with milliseconds. And I must calculate average bitrate in bits per second.

I'm doing this:

SELECT AVG(Bytes*8)/AVG(Milliseconds/1000)
FROM Tracks

Apparently it is wrong. I'm using an app with exercises

I have this result

254492.61

And should be

254400.25

And ?? What is the issue with your query? – Gordon Linoff Aug 11 '17 at 02:15 — Gordon Linoff, Aug 11 '17 at 02:15

score 0 · Answer 1 · answered Aug 11 '17 at 02:17

0

I think you only want one average calculation

SELECT AVG((Bytes*8.0)/(Milliseconds/1000.0))
FROM Tracks

and you may want to increase precision to decimals which is why 8.0 and 1000.0 are used above. Remove if unwanted.

answered Aug 11 '17 at 02:17

Paul Maxwell

score 0 · Answer 2 · answered Aug 11 '17 at 02:20

I would be inclined to write this as:

SELECT SUM(Bytes*8) / SUM(Milliseconds/1000)
FROM Tracks

This is equivalent to your query, though -- assuming that the values are never NULL.

Perhaps they mean the average of averages:

SELECT AVG(Bytes * 8 / (Milliseconds / 1000))
FROM Tracks;

I would not describe this as the average bits per second, however.

2 Answers2