CODEWITHSUNDEEP
sqlgoogle-bigquery
Ipkiss
Ipkiss

Reputation: 801

calculate percentage from BigQuery table Standard SQL

| eta   | arrived    | 
+-------+------------+
| 06:47 |    07:00   | 
| 08:30 |    08:20   | 
| 10:30 |    10:38   | 
+-------+------------+

if have a table like above and i want to calculate how many of the arrived is within the ETA, for example, the first and the third one is within the eta time but the second one is not, so i would like to calculate the percentage or say out of 3, 2 were on time. how would i do this in Bigquery Standard SQL?

Upvotes: 0

Views: 4656

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

i would like to ... say "out of 3, 2 were on time"

Yet another "option" 

#standardSQL
SELECT FORMAT('Out of %i, %i were on time', COUNT(1), COUNTIF(arrived < eta)) summary
FROM `project.dataset.table`

if to apply to dummy data in your question - result is: 

Row summary  
1   Out of 3, 1 were on time

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

One way to do this is:

select avg(case when arrived < eta then 1.0 else 0 end) as ratio_on_time
from t;

You can use 100.0 if you actually want a number from 0 to 100.

Upvotes: 2

Elliott Brossard
Elliott Brossard

Reputation: 33735

Count the number of rows matching the condition, then divide by the total count:

SELECT
  100 * COUNTIF(arrived < eta) / COUNT(*) AS percent_on_time
FROM dataset.table

I reversed the condition since my understanding is that you would want to check that the arrival time was less than the ETA.

Upvotes: 1

Related Questions