Sum union的Sum结果

Question

嘿，我正在尝试将两个不同结果集的结果相加，然后在一个unio中进行合并。目的是得到每个描述的计数的差值。

使用下面的代码，我在MS ACCESS的union语句中得到一个错误。或者，如果我在join中的from I get错误后添加了额外的括号。

有什么想法？

select description, sum(CP)

from

(select  description, count(description)
*-1 AS CP
from status
where date_loaded between #1/1/2014# and #2/1/2014#
group by description) 
Union all
(select  description, count(description) AS CP
from status
where date_loaded between #1/1/2014# and #3/1/2014#
group by description) 

按描述分组

Answer 1

您不能在子查询中执行union (这是MS Access的众多问题之一)。幸运的是，您可以使用条件聚合来做您想做的事情：

select description,
       count(*) - sum(iif(date_loaded between #1/1/2014# and #2/1/2014#, 1, 0)) as diff
from status
where date_loaded between #1/1/2014# and #3/1/2014#
group by description;

我认为下面的代码也能满足您的需求：

select description,
       sum(iif(date_loaded between #2/2/2014# and #3/1/2014#, 1, 0)) as newest
from status
where date_loaded between #1/1/2014# and #3/1/2014#
group by description;