Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
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
 
Old October 17th, 2004, 02:30 PM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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!!



 
Old October 17th, 2004, 04:11 PM
Authorized User
 
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 17th, 2004, 04:38 PM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks!!
What can I add so that if there are 0 enrolled that will show up in the result table?

 
Old October 17th, 2004, 07:19 PM
Authorized User
 
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 17th, 2004, 10:03 PM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

i'm running an access db, so i don't think i can use that outer join. is there another way?

 
Old October 18th, 2004, 10:06 AM
Authorized User
 
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 18th, 2004, 10:27 AM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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??






Similar Threads
Thread Thread Starter Forum Replies Last Post
sql count problem keyvanjan Classic ASP Basics 1 May 5th, 2006 09:37 AM
need help for sql COUNT designwork SQL Language 4 December 29th, 2005 05:26 PM
SQL query for count programmer_kay ADO.NET 1 April 18th, 2004 02:48 AM
SQL query for count programmer_kay SQL Language 1 April 17th, 2004 07:30 PM
sql Count winnie1778 SQL Server 2000 7 November 7th, 2003 02:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.