Aggregate function with three tables having one to many relation ship

[Naqibullah]

Dear colleagues,
Hello to everyone, i have three tables named community, group and member.
Community has one to many relationship with group,
group has one to many relationship with member.
i would like to have a query returning total groups and total members group by community. for example

CommunityName totalGroups totalMembers
A 20 400
B 30 550

I have written following Code

SELECT Community.CommunityName, Count(Group.GroupID) AS CountOfGroupID, (select Count(Member.MemberID) as totalMember
from member inner join group on group.groupid=member.groupid
group by group.groupid) AS CountOfMemberID
FROM Community INNER JOIN Group ON community.CommunityID = group.communityid
GROUP BY Community.CommunityName;

But this doesn't work

 

[plog]

Can you demonstrate this with data? Include 2 sets:

A. Starting sample data from your tables. Include relevant field and table names and enough data to cover all cases.

B. Expected results based on A. What should be produced when you feed it the starting data?

You haven't really made it clear if an individual should be counted twice at the community level if they belong to multiple groups of that community.

 

[Naqibullah]

thanks for the reply,
let me shed light on my scenario, each community can have one or more groups, and same, one group can have one or more members, one specific group can be registered in only one community, and same, a specific member can be registered in only one group, the number of groups in a community is not limited, but the number of members in a group is 15-20 members per group. for example if a community has two groups, then total members for that community should be about 2*20=40 unique members, and likewise if a community has 5 groups, then total members for that community is about 5*20=100 unique members.
hope this helped you in understanding my scenario.
Regards

 

[arnelgp]

select table1.[community name], first(table1.countofcommunityid) as [total group], sum(table2.countofgroupid) as [total member]
from (select community.[community name], count(group.communityid) as countofcommunityid
from community left join [group] on community.communityid = group.communityid
group by community.[community name])  as table1 left join (select community.[community name], member.groupid, count(member.groupid) as countofgroupid
from (community left join [group] on community.communityid = group.communityid) left join member on group.groupid = member.groupid
group by community.[community name], member.groupid)  as table2 on table1.[community name] = table2.[community name]
group by table1.[community name]

 

[Naqibullah]

thanks, worked