mysql - SQL 查询 : Number of applications by month, 县和计划

Question

我需要按月份、县和计划生成支出 list 和申请数量。到目前为止，我已经能够获得支出 list ，但我无法获得每月申请数量的 list 。这是我到目前为止的查询，但申请数量不正确。

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

在此先感谢您的帮助。

Answer 1

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

三种解决方案:

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

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