SQL 101 alias in calculations

[old_pixel]

I'm just starting my SQL with Google Big Query and I have been seaching the web on how to use an alias in calculations....and I can`t figure it out.....HELP 

SELECT

  Date,

  SUM(DISTINCT Total_Bags) AS total_count_bags,

  SUM(DISTINCT Small_Bags) AS small_count_bags,

  (small_count_bags / total_count_bags)*100 AS small_bags_percent,

FROM

  `table_name`

[ms4446]

In BigQuery, you cannot directly reference aliases within the same SELECT statement. This means you can't use small_count_bags and total_count_bags directly in your calculation.

However, there are two workarounds:

1. Repeat the expressions:

SELECT
  Date,
  SUM(DISTINCT Total_Bags) AS total_count_bags,
  SUM(DISTINCT Small_Bags) AS small_count_bags,
  (SUM(DISTINCT Small_Bags) / SUM(DISTINCT Total_Bags)) * 100 AS small_bags_percent
FROM
  `table_name`

2. Use a subquery:

SELECT
  Date,
  total_count_bags,
  small_count_bags,
  (small_count_bags / total_count_bags) * 100 AS small_bags_percent
FROM
(
  SELECT
    Date,
    SUM(DISTINCT Total_Bags) AS total_count_bags,
    SUM(DISTINCT Small_Bags) AS small_count_bags
  FROM
    `table_name`
) AS subquery

In the second method, you create a subquery to calculate the total and small bag counts. Then, in the outer query, you can use the aliases defined in the subquery to calculate the percentage.

Both methods achieve the same result, and you can choose whichever feels more comfortable and readable for you.

[benjikj-cera]

Thanks for the confirmation on this. I find this the most ridiculously obvious missing feature in BigQuery. This seems like an incredibly standard functionality that should be available.

Do you know if this is a feature that is being worked on at all?