2

The code below doesn't work when I replace SUM(Sales) with ROUND(SUM(Sales),2), works otherwise. Sales field type = Double.

SELECT row_number() OVER (PARTITION BY Region) as Row_No, Name as Product, Category, Sales,
SUM(Sales) OVER (PARTITION BY Region, Category) as Category_Sales,
SUM(Sales) OVER (PARTITION BY Region) as Regional_Sales
FROM table
WHERE Region = "North"
ORDER BY Category, Sales;

Error thrown (MySQL 8) : OVER is not valid at this position, expecting ';' Tried using CONVERT() and CAST() to convert the aggregate values to INT. Didn't work either. That said, ROUND() works fine without OVER clause as in the code below.

SELECT Region, Category, ROUND(SUM(Sales),2) as Total_Sales
FROM table
GROUP BY Category;

What's going on and how do I make this work so that aggregate sales values are rounded to d decimals using ROUND(Val, d) alongwith the OVER clause?

Sachin D
  • 73
  • 1
  • 2
  • 8
  • 2
    I would imaging Round has to be over the whole window function..eg round(SUM(Sales) OVER (PARTITION BY Region, Category),2) as Category_Sales – P.Salmon Jul 15 '22 at 07:18
  • Thanks @P.Salmon! That fixed it. I am still not clear as to why it won't work when I try to round just the aggregate values before applying the window function? – Sachin D Jul 15 '22 at 11:14
  • 2
    In a window function the sum is over... ,sum is not executed then window function applied – P.Salmon Jul 15 '22 at 12:17

0 Answers0