- Home
- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges

I have the following database design: Employees Table: EmployeeID, Name, OrgCode Departments Table: OrgCode, DepartName CompleteSurvey Table: ID, ParticipantID And I need to develop one query that will display a table that shows the total number of employees in all departments and the total number of participants who completed the survey in all departments. I could be able to find the total number of employees in all department by the following query: SELECT COUNT(DISTINCT dbo.Employees.EmployeeID) AS [Total Number of Employees] FROM dbo.Departments INNER JOIN dbo.Employees ON dbo.Departments.OrgCode = dbo.Employees.OrgCode CROSS JOIN dbo.CompleteSurvey Then, I could be able to find the total number of participants in all department by the following query: SELECT COUNT(DISTINCT
dbo.CompleteSurvey.ID) AS [Total Number of Participants] FROM dbo.Departments INNER JOIN dbo.Employees ON dbo.Departments.OrgCode = dbo.Employees.OrgCode INNER JOIN dbo.CompleteSurvey ON dbo.Employees.EmployeeID = dbo.CompleteSurvey.RespondantID But I should have a one query only. For example, if Department A has 100 employee and the number of participants is 50 out of 100 and Department B has 80 employee and the number of participants is 30 The query should show the following: **the total number of employees in all departments = 180** **the total number of participants in all departments = 80** **the percent completion in all of them = 80/180 = 44%** **So how to do that?**

sql-server-2008-r2queryquery-designer
Comment

**4** People are following this question.

Copyright 2019 Redgate Software.
Privacy Policy