My table looks like this:
examID action timeSinceStart
a write 300
a answer 2535
b answer 3834
a answer 2900
c write 774
d edit 3674
c answer 3489
a edit 3983
c answer 300
I seek the following output:
examID averageTime
a 2717.5
b 3834
c 1894.5
That is, I only look at the actions
that are answer
, I only write the examID
once and another column with the average time since start at the point of answering
.
Is it possible to do this directly without creating a column for the sum and a column for the count, and then writing the average formula?
My "complicated" approach doesn't work:
%%bigquery df_average_times
SELECT countTimes.examID, sumTimes.answerSum, countTimes.answerCount, (sumTimes/countTimes) as averageTime
FROM
( SELECT examID, timeSinceStart, action, SUM(*) as answerSum
FROM `some-database.bq.results`
GROUP BY examID, timeSinceStart, action
HAVING action='answer'
ORDER BY examID) as sumTimes
INNER JOIN
( SELECT examID, timeSinceStart, action, COUNT(*) as answerCount
FROM `some-database.bq.results`
GROUP BY examID, timeSinceStart, action
HAVING event='answer'
ORDER BY examID) as countTimes
ON falseAnswers.questionId = trueAnswers.questionId
The error states:
ERROR:
400 Argument * can only be used in COUNT(*) at [3:58]
I think you need below query -
SELECT examID, AVG(timeSinceStart)
FROM (SELECT examID, timeSinceStart
FROM `some-database.bq.results`
WHERE action='answer') X
GROUP BY examID;