 |
| SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
October 17th, 2004, 02:30 PM
|
|
Registered User
|
|
Join Date: Oct 2004
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
sql COUNT problem
I'm new to sql and having trouble getting the right query language.
I have two tables
tblCourses:
CourseID courseName
1 Course1
2 Course2
tblRegister:
CustomerID lName CourseID
1 Smith 1
2 Jones 1
3 Bush 2
I'm trying to write a query that will count the records in tblRegister based on CourseID and display the following
CourseID courseName numberEnrolled
1 Course1 2
2 Course2 1
Here is what I have that is not working
'SELECT tblCourses.CourseID, tblCourses.courseName, COUNT(CustomerID) AS numberEnrolled FROM tblCourses LEFT OUTER JOIN tblRegister ON tblCourses.courseID=tblRegister.courseID'
Any help is greatly appreciated!!
|
|
October 17th, 2004, 04:11 PM
|
|
Authorized User
|
|
Join Date: Apr 2004
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Code:
SELECT course.courseID,
course.courseName,
COUNT(*) As numberEnrolled
FROM tblCourses course,
tblRegister reg
WHERE reg.courseID = course.courseID
GROUP BY course.courseID, course.courseName
Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
|
|
October 17th, 2004, 04:38 PM
|
|
Registered User
|
|
Join Date: Oct 2004
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Thanks!!
What can I add so that if there are 0 enrolled that will show up in the result table?
|
|
October 17th, 2004, 07:19 PM
|
|
Authorized User
|
|
Join Date: Apr 2004
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
In that case, you would need to add the appropriate outer join. Assuming your database supports the SQL 99 syntax for outer joins
Code:
SELECT course.courseID,
course.courseName,
COUNT(*) As numberEnrolled
FROM tblCourses course
LEFT OUTER JOIN tblRegister reg
ON reg.courseID = course.courseID
GROUP BY course.courseID, course.courseName
Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
|
|
October 17th, 2004, 10:03 PM
|
|
Registered User
|
|
Join Date: Oct 2004
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
i'm running an access db, so i don't think i can use that outer join. is there another way?
|
|
October 18th, 2004, 10:06 AM
|
|
Authorized User
|
|
Join Date: Apr 2004
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
What version of Access? My understanding is that recent versions of Access support the LEFT OUTER JOIN syntax...
Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
|
|
October 18th, 2004, 10:27 AM
|
|
Registered User
|
|
Join Date: Oct 2004
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
you are right, my version of access does support those joins. i've never done any serious work with a db before, so i really appreciate your help.
The select statement you suggested works, but it returns a value of 1 for those that should have a value of 0. so if there are 0 entries in tblRegister it still returns a value of 1 for numberEnrolled.
i'm not sure whether that is a problem with the sql statement or the db??
|
|
|
