Filtering data using SQL

Question

In R studio, we can subtract the value of column B from Column A and store it to a new column C. Then next step we can calculate, get sum, average and all kinds of calculation on it. How can I do the same thing with SQL in big query? It does not allow me to write two select statements on the same page. how can I save my data to perform more calculations after cleaning or filtering for certain things?

Thank you in advance

I subtracted the values of column A from Column B and put them in a new column "duration". then I got stuck. I was trying to sum the value of duration for customer type X only.

SELECT (ended_at - started_at) AS duration, customer_type FROM my-project-81752.loyal_customer.jan22

my result is;

Row duration         customer_type
1   0-0 0 0:15:5      X
2   0-0 0 0:19:1      X
3   0-0 0 0:11:34     Y
4   0-0 0 0:14:26     X
5   0-0 0 0:3:3       Y
6   
...

now how can I sum the duration to get the total duration for customer X?

do I need to store the result somewhere before using SUM ?

Is bigquery is different than other sql databases?

Answer 1

If you want total duration for customer_type "X" then group by with aggregation should do the trick. Please try this:

SELECT sum(ended_at - started_at) AS total_duration, customer_type FROM my-project-81752.loyal_customer.jan22 where customer_type='X'
group by customer_type