Window Function SUM Calculation

usertkTf1 0
Viewed 29

Hi I am trying to sum the profit but it keeps showing this error message:

No matching signature for aggregate function SUM for argument types: STRUCT<order_date DATE, product_categories STRING, profit FLOAT64>. Supported signatures: SUM(INT64); SUM(FLOAT64); SUM(NUMERIC); SUM(BIGNUMERIC); SUM(INTERVAL) at [20:8]

Does anybody know what happens?

Here is the code I write

WITH profit AS
(
  SELECT DATE(o.created_at) AS order_date,
         p.category AS product_categories,
         (o.sale_price - i.cost) AS profit
  FROM `bigquery-public-data.thelook_ecommerce.order_items` AS o
  INNER JOIN `bigquery-public-data.thelook_ecommerce.inventory_items` AS i
  ON o.product_id = i.product_id
  INNER JOIN `bigquery-public-data.thelook_ecommerce.products` AS p
  ON o.product_id = p.id
  WHERE DATE(o.created_at) >= "2022-06-01" AND DATE(o.created_at) <= "2022-08-15"
  ORDER BY 2,1
)

SELECT order_date,
       product_categories,
       SUM(profit) OVER(PARTITION BY product_categories, EXTRACT(MONTH FROM date) ORDER BY product_categories, date) AS current_mtd
FROM profit
ORDER BY 2,1
google-bigquery
1 Answers

Name of CTE conflicts with name of column - so use below workaround

SELECT order_date,
       product_categories,
       SUM(profit.profit) OVER(PARTITION BY product_categories, EXTRACT(MONTH FROM date) ORDER BY product_categories, date) AS current_mtd
FROM profit
ORDER BY 2,1
Mikhail Berlyant0
Related