结合两个查询功能和平均每个

Question

我有一个查询，执行2列（col1和col2）的计算，然后根据给定的标准（时间= week1和week2）和组的平均结果他们基于另一个标准（ID）。

它看起来像：

SELECT subquery1.[ID], Avg (Param1) as Avg_Param1 
FROM 
(
SELECT [Table1].[ID], [Table1].Time, [col1]/40)/[col2] AS Param1 
FROM Table1 
) AS subquery1 
WHERE (subQuery1.Time = 'week 1' or subquery1.Time = 'Week 2') 
Group By subquery1.[ID] 
; 

现在我想在同一个查询中创建一个名为Avg_param2列的分别是标准就是让说“五周”和“6周”。

我可以在第二个查询中做到这一点，然后加入他们，但我怎么能一次性做到这一点？

Answer 1

试试这个：

SELECT [Table1].[ID], 
     AVG(IIF([Table1].Time IN ('week 1','Week 2') , [col1]/40/[col2] , NULL)) AS Avg_Param1 , 
     AVG(IIF([Table1].Time IN ('week 5','Week 6') , [col1]/40/[col2] , NULL)) AS Avg_Param2 
FROM [Table1] 
Group By [Table1].[ID]