Count number of records in related tables
-
Hi. I have database structure like this: Organizations(OrganizationID, Name) Members(MemberID, OrganizationID, Name) Courses(CourseID, OrganizationID, Name) I need to return the Organization Name, the number of members of that organization and the number of courses in that organization in one sql statement so I'd have a table like [OrganizationName] [Number of Members] [Number of courses] [Org A] [10] [22] [Org B] [92] [11] ... Is it possible without using a stored procedure? Can anyone help me out? Any help would be much appreciated.
-
Hi. I have database structure like this: Organizations(OrganizationID, Name) Members(MemberID, OrganizationID, Name) Courses(CourseID, OrganizationID, Name) I need to return the Organization Name, the number of members of that organization and the number of courses in that organization in one sql statement so I'd have a table like [OrganizationName] [Number of Members] [Number of courses] [Org A] [10] [22] [Org B] [92] [11] ... Is it possible without using a stored procedure? Can anyone help me out? Any help would be much appreciated.
One way:
select o.Name, (select count(*) as MembersCount from Members m where m.OrganizationID= o.OrganizationID),(select count(*) as CoursesCount from Courses c where c.OrganizationID = o.OrganizationID) from Organization oThere are other alternatives depending on what database you are usingBob Ashfield Consultants Ltd
-
One way:
select o.Name, (select count(*) as MembersCount from Members m where m.OrganizationID= o.OrganizationID),(select count(*) as CoursesCount from Courses c where c.OrganizationID = o.OrganizationID) from Organization oThere are other alternatives depending on what database you are usingBob Ashfield Consultants Ltd
Great. Thanks very much!
-
Great. Thanks very much!