Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
OnlineFriend
Starting Member
1 Post |
 Posted - 2010-08-26 : 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 CourseCompletedFROM dbo.tblCOMPANY INNER JOIN dbo.TBLLOCATION ON dbo.tblCOMPANY.CompanyID = dbo.TBLLOCATION.CompanyIDThank you. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
Help to create a query for Usage Information