title: counting methodolegy in SQL Server query
body: <p>Good day everyone…
  I have a database that has the following:
  Division table that linked to Employee table.
  Group table, i.e. courses categories, that linked to Courses table.
  and these table linked together thru Curses_Employee table .
  so far I managed to come up with a query that give me the number of users who toke certain course in each group/category like the following output table:
  groupName | DivisionName| courseName|Total Number of Participants</p>
  
  <p>and here is my query for the above scheme:</p>
  
  <pre><code>SELECT     dbo.groups.GroupName, dbo.Divisions.DivisionName, dbo.courses.CourseName, COUNT(dbo.employee_courses.courseId) AS [Total Number of Participants]
  FROM         dbo.employee AS employee_1 INNER JOIN
                        dbo.Divisions ON employee_1.DivisionCode = dbo.Divisions.SapCode INNER JOIN
                        dbo.employee_courses ON employee_1.Username = dbo.employee_courses.employeeId INNER JOIN
                        dbo.courses ON dbo.employee_courses.courseId = dbo.courses.CourseID INNER JOIN
                        dbo.groups ON dbo.courses.GroupID = dbo.groups.ID
  WHERE     (dbo.courses.GroupID = 2)
  GROUP BY dbo.courses.CourseID, dbo.courses.CourseName, dbo.Divisions.DivisionName, dbo.groups.GroupName
  </code></pre>
  
  <p>now I want to add two columns the total number of employee in each division as well as the % like the following table:
  groupName | DivisionName| courseName|Total Number of Participants |Total Number of Employee |%</p>
  
  <p>I tried this query but it give me an error:</p>
  
  <pre><code>SELECT     dbo.groups.GroupName, dbo.Divisions.DivisionName, dbo.courses.CourseName, COUNT(dbo.employee_courses.courseId) AS [Total Number of Participants],
                            (SELECT     COUNT(Name) AS Expr1
                              FROM          dbo.employee
                              WHERE      (DivisionCode = employee_1.DivisionCode)
                              GROUP BY DivisionCode) AS [Total Number of Employee]
  FROM         dbo.employee AS employee_1 INNER JOIN
                        dbo.Divisions ON employee_1.DivisionCode = dbo.Divisions.SapCode INNER JOIN
                        dbo.employee_courses ON employee_1.Username = dbo.employee_courses.employeeId INNER JOIN
                        dbo.courses ON dbo.employee_courses.courseId = dbo.courses.CourseID INNER JOIN
                        dbo.groups ON dbo.courses.GroupID = dbo.groups.ID
  WHERE     (dbo.courses.GroupID = 2)
  GROUP BY dbo.courses.CourseID, dbo.courses.CourseName, dbo.Divisions.DivisionName, dbo.groups.GroupName
  </code></pre>
  
  <p>the error message: </p>
  
  <blockquote>
    <p>column 'dbo.employee.DivisionCode' is invalid in the select list
    because it is not contained in either as aggregate function or the
    GROUP BY clause.</p>
  </blockquote>