how to sum results of a calculated column-CodePudding

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

CodePudding user response：

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'

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'

CodePudding user response：

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.