'how to sum results of a calculated column

I'm trying to get the total duration of membership types (member&casual) another application of the same problem would be to get the total count of the population and total count of a particular group eg members then use the results to calculate percentage of population that members represent

     member_casual
     started_at,
     ended_at,
        ended_at-started_at as duration
from `cyclistic-case-study-349400.2020_q1.4`
group by member_casual = 'member'```

also tried

```select 
     member_casual
     started_at,
     ended_at,
        sum(ended_at-started_at) as duration
from `cyclistic-case-study-349400.2020_q1.4`
where member_casual = 'member'```

any help will be appreciated
sqlgoogle-bigquerycalculation


Solution 1:[1]

From following query, you can get the duration of each member_casual.

SELECT member_casual,
       SUM(DATE_DIFF(ended_at, started_at, DAY)) AS duration
  FROM `cyclistic-case-study-349400.2020_q1.4`
 GROUP BY 1

For other metrics you mentioned, you need to use a window(analytic) function or need a subquery to get a total population to calculate the percentage.

(update) I've tried it with a test dataset and got the following output.

CREATE OR REPLACE EXTERNAL TABLE `my_dataset.cyclistic_case_study_349400` 
OPTIONS (
  format = 'CSV',
  skip_leading_rows = 1,
  field_delimiter = ',',
  uris = ['https://drive.google.com/open?id=1q8d2nwUTGH33MRpWShU-2dMhjYC1JANJ']
);

SELECT member_casual,
       SUM(DATE_DIFF(ended_at, started_at, DAY)) AS duration
  FROM `my_dataset.cyclistic_case_study_349400`
 GROUP BY 1;


+---------------+----------+
| member_casual | duration |
+---------------+----------+
| member        |      137 |
| casual        |      509 |
+---------------+----------+

Solution 2:[2]

you need to use a date function or datetime functions.

depending what information you need day month you will have to choose, which output format you need

select 
     member_casual
     started_at,
     ended_at,
        sum(DATE_DIFF(ended_at,started_at, DAY)) as duration
from `cyclistic-case-study-349400.2020_q1.4`
where member_casual = 'member'

or

select 
     member_casual
     started_at,
     ended_at,
        sum(DATETIME_DIFF(ended_at,started_at, DAY)) as duration
from `cyclistic-case-study-349400.2020_q1.4`
where member_casual = 'member'

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 nbk

Related Questions