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

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

先谢谢您的帮助。

最佳答案

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

三种解决方案:


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


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

08-26 06:21