|
OnlineFriend
Starting Member
1 Posts |
 Posted - 08/26/2010 : 16:01:11
|
I need to create a query that can count total number of students by companyname and by location name (some companies have more than one location. I also need to find out how many students started the course. Out of those finished the course how many passed the course. The following query is giving me total count but I need total count by company level, location level and possible by user level. This is what I have so far.
SELECT dbo.tblCOMPANY.CompanyName, dbo.TBLLOCATION.LocationName,
(SELECT COUNT(*) AS userCount
FROM dbo.TBLUSER
WHERE (CompanyID = dbo.tblCOMPANY.CompanyID) AND (UserType = 1)) AS StudentCount,
(SELECT COUNT(*) AS courseCount
FROM dbo.TBLCOURSEPASSED
WHERE (UserID IN
(SELECT UserID
FROM dbo.TBLUSER AS TBLUSER_2
WHERE (CompanyID = dbo.tblCOMPANY.CompanyID)))) AS CourseStarted,
(SELECT COUNT(*) AS passCount
FROM dbo.TBLCOURSEPASSED AS TBLCOURSEPASSED_1
WHERE (PassDate IS NOT NULL) AND (UserID IN
(SELECT UserID
FROM dbo.TBLUSER AS TBLUSER_1
WHERE (CompanyID = dbo.tblCOMPANY.CompanyID)))) AS CourseCompleted
FROM dbo.tblCOMPANY INNER JOIN
dbo.TBLLOCATION ON dbo.tblCOMPANY.CompanyID = dbo.TBLLOCATION.CompanyID
Thank you. |
|
Help to create a query for Usage Information
