SQL - 多组

Question

我现在有一个查询按周给我弄一个项目的用法STDEVP：

SELECT Item, SUM(Quantity * PackQty) AS Usage 
FROM [Database].[dbo].Transactions 
WHERE Transactions.Type in ('I') AND Date > DATEADD(year, -1,GETDATE()) 
GROUP BY Item, Datepart(week, MyDate) 
ORDER BY Item 

它的输出就像这样：

**Item** **Usage** 
9999   10 
9999   2 
9999   1 
9999   1 
9999   1 
9999   2 
00125   5 
00135   9 
00135   10 
00135   10 
00159   2 
00159   2 
00159   6 
00159   2 
00159   4 
00159   4 
00159   4 
00159   4 
.....  ... 

我希望得到每个项目的人口是用法的STDEVP。 例如，我在想：

**Item**  **STDEVP** 
9999    3.236 
00125   0 
00135   0.471 
00159   1.323 
.....   ..... 

有没有办法做到这一点？由于

Answer 1

我想这是你想要的东西：

SELECT Item, SUM(Quantity * PackQty), STDEVP(Quantity * PackQty) AS Usage 
FROM [Database].[dbo].Transactions 
WHERE Transactions.Type in ('I') AND Date > DATEADD(year, -1,GETDATE()) 
GROUP BY Item 
ORDER BY Item; 

编辑：

如果你需要它的原始查询，使用子查询：

SELECT Item, STDEVP(qp) AS Usage 
FROM (SELECT Item, Datepart(week, MyDate) as myweek, SUM(Quantity * PackQty) as qp 
     FROM [Database].[dbo].Transactions 
     WHERE Transactions.Type in ('I') AND Date > DATEADD(year, -1, GETDATE()) 
     GROUP BY Item, Datepart(week, MyDate) 
    ) iw 
GROUP BY Item 
ORDER BY Item;