sql - T-SQL- Sum over Partition by 结合 group by

Question

我正在尝试将分区的总和放入普通的 sql 语句中，但代码无法运行。技术:SQL Server 2014

代码如下:

select  
     ZABF$,ZUNR$,ZONR$,FPLN$,ZSTR$,right(left(GABS$,3),2) as Region

    ,SUM(Nettobetrag) AS NettoUmsatz
    ,SUM(BRUTTOBETRAG) AS BruttoUmsatz
    ,SUM(Nettobetrag) over (partition by ZABF$,ZUNR$,ZONR$,FPLN$,right(left(GABS$,3),2))

from
     ArchivZBEW.PROD.sFCTS_G195
where 
    NETTOBETRAG<>0 and right(left(GABS$,3),2)<>'65' 
    and UTYP$ in('K','M','P')
group by
    ZABF$,ZUNR$,ZONR$,FPLN$,ZSTR$,right(left(GABS$,3),2)

如您所见，我尝试获取第一行所有列的输出，然后是不同的总和。 NettoUmsatz 和 Bruttoumsatz 工作正常，如预期的那样 - 但现在我需要另一个忽略“ZSTR$”字段的总和。我把这个查询作为 CTE 的一部分，所以最好把它放在这个语句中，否则我必须两次加入表并且语句变得非常慢。

错误代码:(德语):

Die ArchivZBEW.PROD.sFCTS_G195.NETTOBETRAG-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion und nicht in der GROUP BY-Klausel enthalten ist.

感谢谷歌翻译:

The archiveZBEW.PROD.sFCTS_G195.NETTOBETRAG column is invalid in the selection list because it is not contained in an aggregate function and is not contained in the GROUP BY clause.

Answer 1

将 SUM()OVER() 与 GROUP BY 一起使用没有特定问题，但您可能对与 OVER 相关的 SUM() 的存在感到困惑，并忘记它只是另一个需要的列SUM自己，或者在群里被提及

这行不通:

SELECT
  a,
  SUM(b),
  SUM(c) OVER(PARTITION BY a) --this wont work: c is not mentioned in GROUP BY and this column is not an aggregate in the GROUP  BY sense
FROM
  table
GROUP BY 
  a

你要么必须:

SELECT
  a,
  SUM(b),
  SUM(c) OVER(PARTITION BY a)
FROM
  table
GROUP BY 
  a, c --make c something you group by

或者:

SELECT
  a,
  SUM(b),
  SUM(SUM(c) OVER(PARTITION BY a)) --inner sum is overed, outer is groupbyed
FROM
  table
GROUP BY 
  a

或者:

SELECT
  a,
  SUM(b),
  SUM(SUM(c)) OVER(PARTITION BY a) --inner SUM is groupbyed, outer sum is overed
FROM
  table
GROUP BY 
  a