Error using the with clause I received message " Syntax error: Expected keyword AS but got "(" at [7:14]"

Question

I am having an issue locating the error in my code

I am practicing the WITH CLAUSE IN Big Query and I am trying to create two temporary tables to eventually join

1. first table would for the sum total sales from all the stores (grouping by storeid)
2. second table would be to get the average of those sum total stores
3. the main query would be to find which stores are greater than the average sum total store

here is what I was able to code:

WITH Total_sales as 
    (SELECT s.storeid,
            sum(Unitprice)as sum_sale 
            FROM `g-mail-1234.SALES.sales_info` as s
            GROUP BY storeid),

    AVG_Sale (average_s_sales) as
    (SELECT ROUND(avg(sum_sale),2) as average_s_sales 
      FROM total_sales)

    SELECT * FROM total_sales as ts
    JOIN avg_sale as av
    ON ts.sum_sale > av.average_s_sale

but when I run the code I get a message: Syntax error: Expected keyword AS but got "(" at [7:14]

what I would like to know is:

1. Where is the error?
2. In the future in BigQuery the 'at [7:14]' is this trying to tell me the line the error code is on? because it is on neither line 7 or line 14

Answer 1

I don’t believe BQ CTE syntax allows you to list the columns that the CTE will return. So this line:

    AVG_Sale (average_s_sales) as

should just be:

    AVG_Sale as