CODEWITHSUNDEEP
sqlsql-serverwindow-functions
Mr 2017
Mr 2017

Reputation: 113

Sum over partition not working

I've got some code with the partition function, but it's not working.

I get an error message that says

Incorrect syntax near 'Sales'

Does anyone know why? I looked at the other partition questions, didn't find an answer,

The code (below) is supposed to select PriceZoneID and Sales from the Aggregated Sales History table then sum up the total sales using the OVER function and put that data in a new column called Total Sales.

It should then sum up the sales for each zone using the OVER (PARTITION) expression in a new column called TotalSalesByZone then order the data by Price Zone ID and Sales 

Select PriceZoneID, 
    Sales,
SUM(Sales) OVER () AS Total Sales, 
SUM(Sales) OVER (PARTITION BY PriceZoneID) AS TotalSalesByZone
From AggregatedSalesHistory
ORDER BY PriceZoneID AND Sales;

(Partition By divides the result into Partitions eg Zones)

If you could post the code with the correct answer, it would be greatly appreciated!

Upvotes: 1

Views: 4295

Answers (1)

Thom A
Thom A

Reputation: 95534

Coming out of the comments now, as it's getting a little silly to correct the errors in there. There is 1 typograhical error in your code, and 1 syntax error:

Select PriceZoneID, 
       Sales,
       SUM(Sales) OVER () AS Total Sales, --There's a space in the alias
       SUM(Sales) OVER (PARTITION BY PriceZoneID) AS TotalSalesByZone
FROM AggregatedSalesHistory
ORDER BY PriceZoneID AND Sales; --AND is not valid in an ORDER BY clause

The correct query would be:

Select PriceZoneID, 
       Sales,
       SUM(Sales) OVER () AS TotalSales, --Removed Space
       SUM(Sales) OVER (PARTITION BY PriceZoneID) AS TotalSalesByZone
FROM AggregatedSalesHistory
ORDER BY PriceZoneID, Sales; --Comma delimited

Upvotes: 4

Related Questions