SQL查询:按月,县和程序分类的应用程序数

马洛

我需要按月,县和程序生成支出清单和申请数量。到目前为止,我已经能够获得支出清单,但是我很难获得每月申请数量的清单。这是我到目前为止的查询,但申请数量不正确。

select 
servicecounty AS County,
program,
sum(case when month(entrydate) = 1 and year(entrydate) = 2014 then    totalpaymenttotal else 0 end) as January,
sum(case when month(entrydate) = 2 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as February,
sum(case when month(entrydate) = 3 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as March,
sum(case when month(entrydate) = 4 and year(entrydate) = 2014 then  totalpaymenttotal else 0 end) as April,
sum(case when month(entrydate) = 5 and year(entrydate) = 2014 then  totalpaymenttotal else 0 end) as May,
sum(case when month(entrydate) = 6 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as June,
sum(case when month(entrydate) = 7 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as July,
 sum(case when month(entrydate) = 8 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as August,
sum(case when month(entrydate) = 9 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as September,
sum(case when month(entrydate) = 10 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as October,
sum(case when month(entrydate) = 11 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as November,
sum(case when month(entrydate) = 12 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as December,
sum(case when month(entrydate) = 1 and year(entrydate) = 2015 then totalpaymenttotal else 0 end) as [January 15],
sum(case when month(entrydate) = 2 and year(entrydate) = 2015 then totalpaymenttotal else 0 end) as [February 15],
sum(case when month(entrydate) = 3 and year(entrydate) = 2015 then totalpaymenttotal else 0 end) as [March 15],
sum(case when month(entrydate) = 4 and year(entrydate) = 2015 then totalpaymenttotal else 0 end) as [April 15]
from Sheet$
group by servicecounty, program

UNION  ALL 
select 
servicecounty AS County,
program,
COUNT(case when month(entrydate) = 1 and year(entrydate) = 2014 then ApplicationID else 0 end) as January,
count(case when month(entrydate) = 2 and year(entrydate) = 2014 then ApplicationID else 0 end) as February,
count(case when month(entrydate) = 3 and year(entrydate) = 2014 then ApplicationID else 0 end) as March,
count(case when month(entrydate) = 4 and year(entrydate) = 2014 then ApplicationID else 0 end) as April,
count(case when month(entrydate) = 5 and year(entrydate) = 2014 then ApplicationID else 0 end) as May,
count(case when month(entrydate) = 6 and year(entrydate) = 2014 then ApplicationID else 0 end) as June,
count(case when month(entrydate) = 7 and year(entrydate) = 2014 then ApplicationID else 0 end) as July,
count(case when month(entrydate) = 8 and year(entrydate) = 2014 then ApplicationID else 0 end) as August,
count(case when month(entrydate) = 9 and year(entrydate) = 2014 then ApplicationID else 0 end) as September,
count(case when month(entrydate) = 10 and year(entrydate) = 2014 then ApplicationID else 0 end) as October,
count(case when month(entrydate) = 11 and year(entrydate) = 2014 then ApplicationID else 0 end) as November,
count(case when month(entrydate) = 12 and year(entrydate) = 2014 then ApplicationID else 0 end) as December,
Count(case when month(entrydate) = 1 and year(entrydate) = 2015 then ApplicationID  else 0 end) as [January 15],
Count(case when month(entrydate) = 2 and year(entrydate) = 2015 then ApplicationID  else 0 end) as [February 15],
Count(case when month(entrydate) = 3 and year(entrydate) = 2015 then ApplicationID  else 0 end) as [March 15],
Count(case when month(entrydate) = 4 and year(entrydate) = 2015 then         ApplicationID  else 0 end) as [April 15]
 from Sheet$
group by servicecounty, program
ORDER BY program

该报告应如下所示:

按月,计划和县划分的支出:http : //i.stack.imgur.com/yJ26A.jpg

按月,计划和县划分的应用数量:http : //i.stack.imgur.com/7Aqkk.png

该表包含以下字段:ServiceCounty,TotalPaymentTotal,Program,ApplicationID,EntryDate

先谢谢您的帮助。

戈登·利诺夫(Gordon Linoff)

您在第二个子查询中的计数都返回相同的值。请记住,count()计算非NULL值的数量。并且,0不为null。

三种解决方案:

  • 将更count()改为sum()将更then改为then 1
  • 删除else
  • 将更else 0改为else NULL

这些是我个人喜好的顺序。

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

Android中的Qt和应用程序数据存储

发布免费应用程序,但按月订阅

下载初始应用程序数据

Firebase配额的移动应用程序数

人口分类应用程序的ER图

如何使用WER创建带有应用程序数据和句柄的转储

Firebase身份验证和实时应用程序数据库如何保护自身?

Metro应用程序数据输入表单是否具有保存和取消按钮?

在Linux和Macintosh上查找“应用程序数据”的目录

存储和访问Java应用程序数据的最佳方法

如何同步PostgreSQL数据和ReactNative移动应用程序数据?

洋葱架构访问目录和应用程序数据库

UWP Desktop Bridge和本地应用程序数据虚拟化文件夹位置

等同于MAC-用户名和应用程序数据

使用Powershell在哪里可以找到和检索服务器应用程序数据?

基于 DialogFlow 语音的应用程序数据库搜索(iOS 和 Android)

如何从角度应用程序访问超级分类帐作曲家查询?

通常,在应用程序中的何处放置SQL查询?

烧瓶应用程序将sql查询转换为python

促进Java应用程序中的SQL表查询功能

在WPF应用程序的DataGrid中显示SQL查询的结果

POS应用程序-简化SQL多重查询(MySQL)

azure 移动应用程序查询,如 sql 语句

拦截并更改由应用程序生成的SQL查询

需要SQL查询才能获得测验应用程序

难于查询类似应用程序的论坛的sql

从Python中的TLS连接读取应用程序数据

Angular应用程序数据绑定不起作用

Angular应用程序数据绑定无法显示数据