total number of records from another table

hi experts, i hope i get solution
i have 2 table with one to many relation ship,

Courses                      Employees
------------                   ---------------
ID                               CourseID
Name                         EmployeeName
-------------------------------------------------

Course                       Employees
--------------------        ----------------------------------------
ID          Name          CourseID         EmployeeName
--------------------        ----------------------------------------
1           course1            1                 emp1
2           course2            1                 emp2
                                      2                 emp3
                                      2                 emp4
                                      2                 emp5
-------------------------------------------------------------------

i need to write an sql statement that will return
course ID, course Name, Total number of employee in each course

result:
ID         Name          Emp_Total_No.
---------------------------------------------
1           course1           2
2           course2           3
---------------------------------------------

is there any solution to write it in a single query

looking forward for your suggestions

thank you
LVL 1
AZZA-KHAMEESAsked:
Who is Participating?
 
Lee SavidgeCommented:
Simplest way is a nested query. Haven't tested it but you get the idea.

select ID,
       Name,
       (select count(e.EmployeeName)
          from Emp_Total_No e
         where c.id = e.CourseID
         group by e.EmployeeName) as Emp_Total_No
  from courses c

Open in new window

0
 
Jeff DarlingDeveloper AnalystCommented:
Or you could use a join.

SELECT A.ID
	,A.NAME
	,Count(B.CourseID) AS Emp_Total_No
FROM courses A
JOIN Employees B ON A.ID = B.CourseID
GROUP BY B.courseID
	,A.ID
	,A.NAME

Open in new window

0
 
Habib PourfardSoftware DeveloperCommented:
Subquery:
SELECT  ID ,
        Name ,
        ( SELECT    COUNT(CourseID)
          FROM      Employees
          WHERE     Employees.CourseID = Courses.ID
        ) Emp_Total_No
FROM    Courses

Open in new window

left join:
SELECT  C.ID ,
        C.Name ,
        COUNT(E.CourseID) Emp_Total_No
FROM    dbo.Courses C
        LEFT JOIN dbo.Employees E ON C.ID = E.CourseID
GROUP BY C.ID ,
        C.Name

Open in new window


PS.
 Lee Savidge's solution: no need to group by e.EmployeeName in subquery
jeffld's solution: no need to group BY B.courseID
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Jeff DarlingDeveloper AnalystCommented:
Thanks Pourfard.  You are correct.

SELECT A.ID
	,A.NAME
	,Count(B.CourseID) AS Emp_Total_No
FROM courses A
JOIN Employees B ON A.ID = B.CourseID
GROUP BY A.ID
	,A.NAME

Open in new window

0
 
Mahesh BhutkarCommented:
SELECT ID, NAME, Count(CourseID) as Emp_Total_No.
FROM courses,Employees where ID = CourseID GROUP BY ID, NAME
0
 
AZZA-KHAMEESAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.