分区总和不起作用 [英] 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

'Sales' 附近的语法不正确

Incorrect syntax near 'Sales'

有人知道为什么吗?看了其他分区问题，没找到答案，

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

代码(下面)应该从 Aggregated Sales History 表中选择 PriceZoneID 和 Sales，然后使用 OVER 函数总结总销售额，并将该数据放入名为 Total Sales 的新列中.

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.

然后应该在名为 TotalSalesByZone 的新列中使用 OVER (PARTITION) 表达式总结每个区域的销售额，然后按价格区域 ID 和销售额对数据进行排序

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 将结果划分为 Partitions，例如 Zones)

(Partition By divides the result into Partitions eg Zones)

如果您能发布带有正确答案的代码，将不胜感激！

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

推荐答案

现在退出评论，因为纠正那里的错误有点愚蠢.您的代码中有 1 个印刷错误和 1 个语法错误:

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

正确的查询是:

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

这篇关于分区总和不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！