Calculating the difference in sum() between different days in SQL

1
votes

I have a query where I sum the values from a column WHERE the date = yesterday (current_date - 1):

SELECT sum(col) as sumCol
from `project.dataset.table`
where date = date_sub(current_date, interval 1 day)

What I want to achieve is to calculate the difference between the result of the above and the result of the same query for the day before i.e. where date = date_sub(current_date, interval 2 day)

I am not sure how to create this calculation within a query. I can union the above with the same query for the previous day, but obviously that gives me a row for each day rather than calculating sum(col) interval 1 day - interval 2 day?

Any help would be appreciated to point me in the right direction

Thanks

2
sqlgoogle-bigquery

2 Answers

2
votes

Well, you can use conditional aggregation:

select sum(case when date = date_sub(current_date, interval 1 day) then col else - col end) as diff
from `project.dataset.table`
where date in ( date_sub(current_date, interval 1 day), date_sub(current_date, interval 2 day) )

EDIT:

More generally, you can get the two values using conditional aggregation:

select sum(case when date = date_sub(current_date, interval 1 day) then col  end) as yesterday,
       sum(case when date = date_sub(current_date, interval 2 day) then col  end) as day_before
from `project.dataset.table`
where date in ( date_sub(current_date, interval 1 day), date_sub(current_date, interval 2 day) )

You can then combine those columns how you want. If a value is missing it will be NULL, so you are safe for division and missing values.

0
votes

are you finding something like below

   SELECT
    ( sum(col) as sumCol
    from `project.dataset.table`
    where date = date_sub(current_date, interval 1 day)
     )-( sum(col) as sumCol
    from `project.dataset.table`
    where date = date_sub(current_date, interval 2 day)
     ) from project.dataset.table