连接MS ACCESS中两个不同的计数查询

Question

我正在努力合并MS Access中的两个以下查询。第一个查询基本上计算了一个产品(pcode)在每周的商店数量。第二个查询每周计算商店的数量。

我想要完成的是有一个表在最后显示WEEK_ID, PCODE, store_number_per_pcode_per_week, store_number_per_week。你能给我一些提示吗?

<标题>1。查询

SELECT cd.WEEK_ID, cd.PCODE, count(cd.STORE_ID) as store_number_per_pcode_per_week
FROM (SELECT DISTINCT STORE_ID, PCODE, WEEK_ID
FROM SALES
) as cd
GROUP BY cd.WEEK_ID, cd.PCODE;

<标题>2。查询

SELECT df.WEEK_ID, count(df.STORE_ID) as store_number_per_week
FROM (SELECT DISTINCT STORE_ID, WEEK_ID
FROM SALES
) as df
GROUP BY df.WEEK_ID;

Answer 1

您可以使用JOIN:

SELECT wp.WEEK_ID, wp.PCODE, wp.store_number_per_pcode_per_week,
w.store_number_per_week
FROM (SELECT cd.WEEK_ID, cd.PCODE,
COUNT(cd.STORE_ID) as store_number_per_pcode_per_week,
FROM (SELECT DISTINCT STORE_ID, PCODE, WEEK_ID
FROM SALES
) as cd
GROUP BY cd.WEEK_ID, cd.PCODE
) wp INNER JOIN
(SELECT cd.WEEK_ID, 
COUNT(cd.STORE_ID) as store_number_per_week,
FROM (SELECT DISTINCT STORE_ID, WEEK_ID
FROM SALES
) as cd
GROUP BY cd.WEEK_ID
) w
ON wp.WEEK_ID = w.WEEK_ID;