Calculate Percentage of each Activity-CodePudding

I have my walking (step count) collected by my smart watch. I have classified physical activity as very low, low, moderate, high and very high based on the number of steps. I am able to get total number of steps that qualify for each physical activity with the below Syntax:

SELECT
 (case when Step_count < 1000 then 'Very Low Physical Activity'
             when Step_count >= 1000 and Step_count <2500 then 'Low Physical Activity'
             when Step_count >= 2500 and Step_count< 5000 then 'Moderate Physical Activity'
             when Step_count >= 5000 and Step_count <10000 then 'High Physical Activity'
             when Step_count > 10000 then 'Very High Physical Activity'
             else 'no_physical_activity'
        end) as Type_of_Physical_Activity,
       COUNT(*) as num_of_times,
       SUM(Step_count) as Total_steps       
                     from `my-second-project-370721.my_activity_2022.my_activity`
              Where Step_count is not NULL
       group by Type_of_Physical_Activity
       order by MIN(Step_count)

How do I get the percentage of each physical activity that can be embedded into this query? (Ex: very low physical activity = 5%, low physical activity = 10% and so on...)

I tried:

SELECT
 (case when Step_count < 1000 then 'Very Low Physical Activity'
             when Step_count >= 1000 and Step_count <2500 then 'Low Physical Activity'
             when Step_count >= 2500 and Step_count< 5000 then 'Moderate Physical Activity'
             when Step_count >= 5000 and Step_count <10000 then 'High Physical Activity'
             when Step_count > 10000 then 'Very High Physical Activity'
             else 'no_physical_activity'
        end) as Type_of_Physical_Activity,
       COUNT(*) as num_of_times,
       SUM(Step_count) as Total_steps,
       ROUND(avg((Step_count)/(Total_steps-Step_count))*100,3) as ActivityPercentage
                     from `my-second-project-370721.my_activity_2022.my_activity`
              Where Step_count is not NULL
       group by Type_of_Physical_Activity
       order by MIN(Step_count)

But am getting error as Unrecognized name: Total_steps

CodePudding user response：

Try wrapping your query like this:

SELECT
    Type_of_Physical_Activity,
    num_of_times,
    Total_steps,
    ROUND(100*Total_steps/(SUM(Total_steps) OVER()), 3) as ActivityPercentage
FROM (
    ...your query... 
    ) query1;

This is so you can make use of the alias Total_steps that you define in the inner query.

CodePudding user response：

You can not use a field alias on another field, so you need to replace this:

avg((Step_count)/(Total_steps-Step_count))*100

by this:

SUM(Step_count)/Step_count*100

To calculate the percentage you are looking for