How do i count number of times a specific data occurred in a raw, BigQuery Standard SQL

Question

say i have this table,

| eta | arrived | time_diff | +-------+----------+-----------+ | 06:47 | 06:47 | 0 | | 08:30 | 08:40 | 10 | | 10:30 | 10:40 | 10 | | 10:30 | 10:31 | 1 | +-------+----------+-----------+ and i got the time_diff by TIME_DIFF(arrived , eta , MINUTE) as time_diff

what I wanted to do is to be able to count how many 0, 10 ... I have. ideally, the above table will yield one 0, two 10 and one 1. Offcorse i don't know in advance the time_diff result just wanted to count how many times a result occurred say i may have 2,3,5... how do I accomplish this in BigQuery Standard SQL?

Eugene · Accepted Answer · 2018-08-29 16:26:18Z

1

You should use group by clause

Select time_diff , Count(*) From [table] Group by time_diff

answered Aug 29, 2018 at 16:26

Eugene

1,54310 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mikhail Berlyant · Accepted Answer · 2018-08-29 16:57:43Z

Below is for BigQuery Standard SQL

From practical standpoint, I would recommend grouping by bins: 0-9, 10-19, 20-29 and so on as it is in below example

#standardSQL WITH `project.dataset.table` AS ( SELECT '06:47' eta, '06:47' arrived UNION ALL SELECT '08:30', '08:40' UNION ALL SELECT '10:30', '10:40' UNION ALL SELECT '10:30', '10:31' ) SELECT FORMAT('%i - %i', bin, bin + 9) bin, cnt FROM ( SELECT 10 * DIV(TIME_DIFF(PARSE_TIME('%R', arrived) , PARSE_TIME('%R', eta) , MINUTE), 10) bin, COUNT(1) cnt FROM `project.dataset.table` GROUP BY bin ) ORDER BY bin

with result

Row bin cnt 1 0 - 9 2 2 10 - 19 2

in case if you need exact distribution per time_diff as is - you can use below

#standardSQL WITH `project.dataset.table` AS ( SELECT '06:47' eta, '06:47' arrived UNION ALL SELECT '08:30', '08:40' UNION ALL SELECT '10:30', '10:40' UNION ALL SELECT '10:30', '10:31' ) SELECT TIME_DIFF(PARSE_TIME('%R', arrived) , PARSE_TIME('%R', eta) , MINUTE) diff, COUNT(1) cnt FROM `project.dataset.table` GROUP BY diff ORDER BY diff

with result as

Row diff cnt 1 0 1 2 1 1 3 10 2

Collectives™ on Stack Overflow

How do i count number of times a specific data occurred in a raw, BigQuery Standard SQL

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related