How to add a column of total number of rows in BigQuery

Question

I have a table in BigQuery where I save a DATESTAMP and temperature sensor value. I usually filter the query by dates. My goal is to add a column in this query where it puts the total number of rows.

For example, if I run this query I have this result:

SELECT DATESTAMP, Temperature
FROM
    `my_project.my_folder.my_table`
WHERE
    DATESTAMP BETWEEN TIMESTAMP("2020-02-05 00:00:00.000")
    AND TIMESTAMP("2020-02-06 00:00:00.00")

ROW DATESTAMP             Temperature
1    2020-02-05 06:44:37 UTC    15.14
2    2020-02-05 09:41:11 UTC    18.25
3    2020-02-05 12:11:25 UTC    21.21
4    2020-02-05 22:15:37 UTC    14.65

And when I run this query I have this result:

SELECT count(*) AS num_total
FROM
    `my_project.my_folder.my_table`
WHERE
    DATESTAMP BETWEEN TIMESTAMP("2020-02-05 00:00:00.000")
    AND TIMESTAMP("2020-02-06 00:00:00.00")

ROW num_total
1   4

My goal is to program a query and receive this result

ROW DATESTAMP             Temperature num_total
1    2020-02-05 06:44:37 UTC    15.14    4
2    2020-02-05 09:41:11 UTC    18.25    4
3    2020-02-05 12:11:25 UTC    21.21    4
4    2020-02-05 22:15:37 UTC    14.65    4

How can I do?

Answer 1

You can do a window count:

SELECT datestamp, Temperature, COUNT(*) OVER() num_total
FROM `my_project.my_folder.my_table`
WHERE 
    datestamp     >= TIMESTAMP("2020-02-05 00:00:00.000") 
    AND datestamp <  TIMESTAMP("2020-02-06 00:00:00.000")

Note that I rewrote your date filter to use an half-open interval instead of between: likely, you do not want the upper bound to be inclusive in the date range.