sql server 运行总数超过，分区 [英] sql server running total with over, partition

问题描述

我正在尝试在以下查询中计算运行总数

I am trying to calculate running total in following query

select 
    a.ICode, MONTH(a.VDate), YEAR(a.vdate)
    , sum(isnull(a.qty, 0))
    , sum(isnull(a.qty, 0)) OVER (partition by a.icode order by a.icode) AS 'total' 
from 
    t_Stock as a
group by 
    a.ICode, MONTH(a.VDate), YEAR(a.vdate)
order by 
    a.icode, YEAR(a.vdate), MONTH(a.VDate)

但我收到一个错误:

消息 8120，级别 16，状态 1，第 3 行
列t_Stock.Qty"在选择列表中无效，因为它既不包含在聚合函数中也不包含在 GROUP BY 子句中.

Msg 8120, Level 16, State 1, Line 3
Column 't_Stock.Qty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

为什么 't_Stock.Qty' 需要在 GROUP BY 子句中，因为我已经在使用聚合函数 (Sum(a.Qty)) ?

Why does 't_Stock.Qty' needs to be in GROUP BY clause as I am already using aggregate function (Sum(a.Qty)) ?

一些演示数据:

icode   vtype   qty   vdate 
32114   Sales   -2    2013-06-03 18:09:17.953 
33459   Sales   -1    2013-06-03 19:39:59.843 
34446   Sales   -1    2013-06-03 20:46:17.030 
39914   Tra     -3    2014-01-07 13:02:31.000
30899   Sales   -1    2013-06-04 11:48:06.267 
25676   Sales   -3    2013-06-04 17:34:01.470 
32126   Sales   -1    2013-06-04 18:12:44.267 
34688   Sales   -1    2013-06-04 18:40:52.750 
31550   Sales   -1    2013-06-04 19:26:40.937 
32795   Sales   -1    2013-06-05 12:03:00.250 

推荐答案

当使用带有聚合的窗口函数时，您实际上需要嵌套结果.这是您的查询版本应该可以工作:

When using window functions with aggregation, you actually need to nest the results. Here is a version of your query that should work:

select a.ICode, MONTH(a.VDate), YEAR(a.vdate),
       sum(isnull(a.qty, 0)),
       sum(sum(isnull(a.qty, 0))) OVER (partition by a.icode order by a.icode) AS total
from t_Stock a
group by a.ICode, MONTH(a.VDate), YEAR(a.vdate)
order by a.icode, YEAR(a.vdate), MONTH(a.VDate);

我不确定你打算用这个做什么.order by 包含与 partition by 相同的值是不寻常的.我希望是这样的:

I'm not sure what you intend to do with this. Having the order by contain the same value as the partition by is unusual. I would expect something like this:

select a.ICode, MONTH(a.VDate), YEAR(a.vdate),
       sum(isnull(a.qty, 0)),
       sum(sum(isnull(a.qty, 0))) OVER (partition by a.icode
                                        order by year(a.vdate), month(a.vdate)
                                       ) AS total
from t_Stock a
group by a.ICode, MONTH(a.VDate), YEAR(a.vdate)
order by a.icode, YEAR(a.vdate), MONTH(a.VDate);

这篇关于sql server 运行总数超过，分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！