Calculating the average of order value without using a WITH statement

userEbIl1 0
Viewed 27

I am trying to add a new column to my table which will be the average value calculated as the division of two existing columns. Therefore Average value = Total Sales / Number of Orders.

My data looks like this:click to view picture

I don't understand why Example Code A does not work but Example Code B does. Please can someone explain?

Example Code 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;

Example Code 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;

Thank you!

sqlite sql
1 Answers

Depending on the DBMS some allow you to reference the alias in the calculation (in the same select) and others require you to either bring it outside in an outer query or state your previous aggregation/functions, such as counts or sums.

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