Trying to calculate Total Sales like below with the following query, But getting not a group by error.

select country,month,sales,sum(sales) over (partition by country order by month) 
from check2
group by country,month,sales

Data

Expected Output

CodePudding user response：

Don't use GROUP BY and, if you want the total for the entire partition then, don't use ORDER BY in the analytic function:

select country,
       month,
       sales,
       sum(sales) over (partition by country) As total_sales
from   check2

Which, for the sample data:

CREATE TABLE check2(country, month, sales) AS
SELECT 'US',     202204, 2000 FROM DUAL UNION ALL
SELECT 'US',     202205, 3000 FROM DUAL UNION ALL
SELECT 'US',     202206, 5000 FROM DUAL UNION ALL
SELECT 'Canada', 202204, 8000 FROM DUAL UNION ALL
SELECT 'Canada', 202205, 2000 FROM DUAL UNION ALL
SELECT 'Canada', 202206, 1000 FROM DUAL;

Outputs:

fiddle

CodePudding user response：

If you're using OVER..PARTITION BY, then you don't need the GROUP BY.

Just this should be sufficient

select country,month,sales,sum(sales) over (partition by country order by month) 
from check2

See example here on LiveSL