显示出group后count为0的数据

[空空法师]

在归类统计的时候有什么办法显示出含有0的数据?要在Access中可以运行。

比如有一个统计一个小组男生，女生数量的query:

SELECT [Group], Gender, count(*) AS [Count]
FROM MyTable
GROUP BY [Group], Gender

如果这个组没有男生在结果中就没有显示。但我要显示出0。这样的结果就比较整洁，并便于处理数据。比如

A Male 2
A Female 3
B Male 0
B Female 4

但现在的情况是

A Male 2
A Female 3
B Female 4

中间缺少一个。有什么简单的办法解决这个问题？多谢！

[WWWWA]

SELECT [Group], Gender, count(*) AS [Count]
FROM
(SELECT 'A' AS [Group],'Male' AS Gender,0
UNION
SELECT 'A' AS [Group],'Female' AS Gender,0
UNION
SELECT 'B' AS [Group],'Male' AS Gender,0
UNION
SELECT 'B' AS [Group],'Female' AS Gender,0
UNION
SELECT * FROM MyTable)
GROUP BY [Group], Gender

OR

将A、B、MALE、FEMALE写入临时表再与工作表连接，再GROUP

[OracleRoob]

select c.[Group],c.[Gender],nz(t.[Count] ,0) as [count]
from 
(select [Group], Gender, count(*)   AS   [Count] from MyTable Group by [Group], Gender ) as t
  right join
  (select * from 
      (select [group] from MyTable  group by [group]) as a,
      (select [Gender] from MyTable  group by [Gender]) as b
  ) as c on t.[Group]=c.[Group] and t.[Gender]=c.[Gender]

[liuyann]

简单
keep it simple

[OracleRoob]

keep it simple
------------------------

？？

[liuyann]

[liuyann]

[liuyann]

TRANSFORM Count(Gender) AS GenderOfCount
SELECT Group
FROM MyTable
GROUP BY Group
PIVOT Gender;

[WWWWA]

OR
TRANSFORM Count(Gender) AS GenderOfCount SELECT Group FROM MyTable GROUP BY Group
PIVOT Gender IN("MALE","FEMALE")