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

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

This is one option.

SELECT DATESTAMP
       , Temperature
       , (select count(*) from `my_project.my_folder.my_table`) 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")

Advertisement

Answer