I have to calculate the avg of gross revenue on bigquery (the key is item_id)
SELECT
t0.order_create_date AS day,
t0.site_country AS country,
p0.product_brand AS brand,
p0.product_gender AS gender,
p0.product_department AS department,
t0.item_qty AS items_sold,
t0.item_sale_price AS gross_revenue,
t0.item_net_price AS net_revenue,
FROM
transactions t0
LEFT JOIN
products p0
ON
t0.item_id = p0.item_id
ORDER BY
country,
day ASC
I tried this :
SELECT
t0.order_create_date AS day,
t0.site_country AS country,
p0.product_brand AS brand,
p0.product_gender AS gender,
p0.product_department AS department,
t0.item_qty AS items_sold,
t0.item_sale_price AS gross_revenue,
AVG(t0.item_sale_price) AS average_value,
t0.item_net_price AS net_revenue,
FROM
transactions t0
LEFT JOIN
products p0
ON
t0.item_id = p0.item_id
ORDER BY
country,
day ASC
Biquery result : SELECT list expression references t0.order_create_date which is neither grouped nor aggregated at [2:3]
Please someone could help me ? :)
