计算组和子组的数量

发布于 2024-12-19 22:13:42 字数 652 浏览 2 评论 0原文

我使用 SQL Server 2008。有一个包含项目、其状态和日期的表：

ID    Status      Date
1     Gone        21-10-2011
2     Cancelled   01-10-2011
3     Won         02-10-2011
4     Won         07-10-2011
5     Gone        02-01-2010
6     Won         24-01-2010

出于统计目的，我需要一些 T-SQL 查询，结果将返回如下表：

Month    Year    Total    Lost   Won
10       2011    4        2      2
01       2010    2        1      1

其中：

Total 表示唯一月-年对的总条目数。

丢失是指状态为“已消失”或“已取消”的唯一月-年对的总条目数

。赢取是指状态为“赢”的唯一月-年对的总条目数。

我了解如何获取唯一月年的总金额，但不知道如何添加结果集“丢失”和“赢得”列并获取条目金额。当然，如果月-年对没有任何“赢得”项目而只有“丢失”，则“赢得”列的值应该为 0。

原文

I work with SQL Server 2008. There is table with projects, their status and date:

ID    Status      Date
1     Gone        21-10-2011
2     Cancelled   01-10-2011
3     Won         02-10-2011
4     Won         07-10-2011
5     Gone        02-01-2010
6     Won         24-01-2010

For statistics purposes I need some T-SQL query that in result will return table like this:

Month    Year    Total    Lost   Won
10       2011    4        2      2
01       2010    2        1      1

Where:

Total means total entries amount for unique Month-Year pair.

Lost means total entries amount for unique Month-Year pair with status 'Gone' or 'Cancelled'

Won means total entries amount for unique Month-Year pair with status 'Won'.

I understand how can I get total amount for unique Month-Year, but don't know how to add in result set Lost and Won columns and get entries amount. And of course if there are no any 'Won' projects for Month-Year pair and only 'Lost' it should be 0 value for Won column.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

拥有 2024-12-26 22:13:42

SELECT
   MONTH([Date]) [Month],
   YEAR([Date]) [Year],
   COUNT(*) [Total],
   COUNT(CASE WHEN Status IN ('Gone', 'Cancelled') THEN 1 END) [Lost],
   COUNT(CASE WHEN Status= 'Won' THEN 1 END) [Won]
FROM
   MyTable
GROUP BY
   MONTH([Date]),
   YEAR([Date])

这是有效的，因为 CASE 中的静默 ELSE 给出 NULL，并且 COUNT 将忽略它

SELECT
   MONTH([Date]) [Month],
   YEAR([Date]) [Year],
   COUNT(*) [Total],
   COUNT(CASE WHEN Status IN ('Gone', 'Cancelled') THEN 1 END) [Lost],
   COUNT(CASE WHEN Status= 'Won' THEN 1 END) [Won]
FROM
   MyTable
GROUP BY
   MONTH([Date]),
   YEAR([Date])

This works because the silent ELSE in the CASE gives NULL, and COUNT will ignore this

回复收藏 0 原文

~没有更多了~