SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help to create a query for Usage Information
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

OnlineFriend
Starting Member

1 Posts

Posted - 08/26/2010 :  16:01:11  Show Profile  Reply with Quote
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.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/28/2010 :  00:59:49  Show Profile  Reply with Quote
you may be better posting table structure with some sample data and explain what you want. see guidelines below

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000