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屋!