Sum over partition not working

Question

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!

What is your error? Expected result? Sample data? And why do you want total sum on all columns? Thats likely where your error is — SqlKindaGuy
Your problem is your choice of alias, specially AS Total Sales. A column's alias/name cannot contain a whitespace, or other special characters, unless the alias/name is quoted: AS [Total Sales] Ideally, however, don't use special characters in your object names, aliases. CamelCase is perfectly acceptable: AS TotalSales — Larnu
Hi Thomas, thanks for the prompt response. I get an error that says - Incorrect syntax near 'Sales.' What would be a better way of writing the query instead using the partition function? Can you please advise? TIA. — Mr 2017
SUM(Sales) OVER (PARTITION BY PriceZoneID) AS TotalSalesByZone . It will also throw error. Because partition requires order by some column. — saravanatn
@saravanatn no it doesn't. Adding an ORDER BY on a SUM() (PARTITION BY...) would cause the SUM to be a "running total", not a cumulative total. — Larnu

Larnu Larnu · Accepted Answer · 2018-07-05T10:44:49

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

Sum over partition not working

1 Answers