统计跨多个列的日期事件

Question

我已经创建了以下内容，但它一直出现错误消息。您试图执行一个不包括指定表达式'ICE Team'作为聚合函数的一部分的查询。

SELECT ztSub.[Master Sheet].[ICE Team], ztSub.[date], Count(ztSub.[Count])
FROM (SELECT [Master Sheet].[ICE Team],[Master Sheet].[Visit Date (planned for)] AS [date],Count([Master Sheet]![Visit Date (planned for)]) AS [Count]
FROM [Master Sheet]
UNION
SELECT [Master Sheet].[ICE Team],[Master Sheet].[Date retasked to?] AS [date], Count ([Master Sheet]![Date retasked to?]) AS [Count]
FROM [Master Sheet] )  AS ztSub
GROUP BY ztSub.[Master Sheet].[ICE Team];

Answer 1

现在可以工作了。我把[日期计数]加起来，结果就出来了。

SELECT [Total].[Ice Team] AS [Ice Team], ztSub.Period, Sum(ztSub.[Count of Dates]) AS  [SumOfCount of Dates]
FROM (SELECT Total.[Ice Team], Total.[Re-Visited] AS Period, Count([Total]![Re-Visited]) AS [Count of Dates]
FROM Total
GROUP BY [Ice Team], [Re-Visited]
UNION SELECT Total.[Ice Team], Total.Visited AS Period, Count([Total]![Visited]) AS [Count of Dates]
FROM Total
GROUP BY [Ice Team], Visited)  AS ztSub
GROUP BY [Total].[Ice Team], ztSub.Period;

Answer 2

如果您要查找的唯一结果是日期列表和出现次数计数，则可能需要尝试使用联合查询:

SELECT date, count(*)
FROM (
(SELECT date_col1 AS date
FROM table1)
UNION
(SELECT date_col2 AS date
FROM table1) )
GROUP BY date

编辑更新的问题:

您的错误来自于您在内部查询中执行计数，但在外部查询中执行分组。如果你单独查看一个内部查询:

SELECT [Master Sheet].[ICE Team],[Master Sheet].[Visit Date (planned for)] AS [date],Count([Master Sheet]![Visit Date (planned for)]) AS [Count]
FROM [Master Sheet]

，我们仍然得到相同的误差。这是由于您在select子句中执行日期计数而没有指定如何处理[ICE Team]值造成的。您可能希望为[ICE Team]值包含分组条款。您已经为外部查询执行了此操作，但在这个内部查询中也应该执行此操作。对于你的Visit Date (planned for)值也是一样的，否则你会得到同样的错误。

尝试这个查询，虽然我不确定它会给你想要的结果。(为此，您必须在您的问题中添加一个输入和预期输出的清晰示例。)

SELECT ztSub.[Master Sheet].[ICE Team], ztSub.[date], Count(ztSub.[Count])
FROM (SELECT [Master Sheet].[ICE Team],[Master Sheet].[Visit Date (planned for)] AS [date],Count([Master Sheet]![Visit Date (planned for)]) AS [Count]
FROM [Master Sheet]
group by [ICE Team],[Visit Date (planned for)]
UNION
SELECT [Master Sheet].[ICE Team],[Master Sheet].[Date retasked to?] AS [date], Count ([Master Sheet]![Date retasked to?]) AS [Count]
FROM [Master Sheet]
group by [ICE Team], [Date retasked to?] )  AS ztSub
GROUP BY ztSub.[Master Sheet].[ICE Team],date;