我正在尝试将一个新列添加到我的表中，该列将是通过两个现有列的除法计算得出的平均值。因此，平均值=总销售额/订单数量。
我的数据如下：click to view picture
我不明白为什么示例代码A不起作用而示例代码B起作用。有人能解释一下吗？

代码A示例

%%sql
    SELECT
        c.country,
        count(distinct c.customer_id) customer_num,
        count(i.invoice_id) order_num,
        ROUND(SUM(i.total),2) total_sales,
        order_num / total_sales avg_order_value
        FROM customer c
    LEFT JOIN invoice i ON c.customer_id = i.customer_id
    GROUP BY 1
    ORDER BY 4 DESC;

代码B示例

%%sql

WITH
    customer_sales AS
    (
    SELECT
        c.country,
        count(distinct c.customer_id) customer_num,
        count(i.invoice_id) order_num,
        ROUND(SUM(i.total),2) total_sales
        FROM customer c
    LEFT JOIN invoice i ON c.customer_id = i.customer_id
    GROUP BY 1
    ORDER BY 4 DESC
    )
    
SELECT
    country,
    customer_num,
    order_num,
    total_sales,
    total_sales / order_num avg_order_value
FROM customer_sales;

谢谢!