Select query - count does not return correct result

sunder

I have "enrollment" table with the following structure:
CREATE TABLE enrollment (
enrollmentID bigint(20) NOT NULL auto_increment,
coursename varchar(200) NOT NULL default '',
studentID int(4) NOT NULL default '0',
semestername varchar(100) NOT NULL default '',
PRIMARY KEY (enrollmentID)
)

This table is populated with coursenames that students have enrolled in a particular semester.

Given a semestername and coursename, my Select query is trying to retrieve all the coursenames and count of the number of students enrolled in each of the courses for the given semester (except for the given course).

SELECT a.coursename, COUNT( * ) AS count
FROM enrollment AS a, enrollment AS b
WHERE b.coursename = "CSC001 - abcdef"
AND b.semestername = "Fall 2005"
AND a.coursename != "CSC001 - abcdef"
AND a.studentID = b.studentID
ORDER BY count
GROUP BY a.coursename
LIMIT 10;

Basically I'm trying to get the courses that should not be scheduled at the same time as that of the given course.

The query is retrieving the correct courses that I need, but the count returned is incorrect.

Any help is appreciated.

Thanks,
Sunder.

edcellgavin

try replacing (*) with (b.coursename)

sunder

Tried replacing (*) with b.coursename, still having the same issue, query does not reflect GROUP BY clause.

edcellgavin

try this,

SELECT a.coursename, COUNT( b.coursename) AS count
FROM enrollment AS a, enrollment AS b
WHERE a.coursename = "CSC001 - abcdef"
AND a.semestername = "Fall 2005"
AND a.coursename != "CSC001 - abcdef"
AND a.studentID = b.studentID
ORDER BY count
GROUP BY a.coursename
LIMIT 10;

sunder

SELECT a.coursename, COUNT( b.coursename ) AS count
FROM enrollment AS a, enrollment AS b
WHERE a.coursename = "CSC001 - abcdef"
AND a.semestername = "Fall 2005"
AND a.coursename != "CSC001 - abcdef"
AND a.studentID = b.studentID
GROUP BY a.coursename
ORDER BY count DESC
LIMIT 0 , 30

executed the above query, no result is displayed except for "Your SQL-query has been executed successfully"

edcellgavin

i've overlooked the query. it really wouldn't return any value... 🙂

check this out
SELECT a.enrollmentid, a.coursename, count(b.studentid)
FROM enrollment AS a inner join enrollment as b on a.enrollmentid = b.enrollmentid
WHERE a.coursename = "CSC001 - abcdef"
AND a.semestername = "Fall 2005"
GROUP BY a.coursename

sunder

SELECT a.enrollmentid, a.coursename, count( b.studentid )
FROM enrollment AS a
INNER JOIN enrollment AS b ON a.enrollmentid = b.enrollmentid
WHERE a.coursename = "CSC001 - abcdef"
AND a.semestername = "Fall 2005"
GROUP BY a.coursename
LIMIT 0 , 30

The above query returns the count of the number of students enrolled in "CSC001 - abcdef" for "Fall 2005".

What I need is all the coursenames that students who take "CSC001 - abcdef" will also take (other than "CSC001 - abcdef") for the "Fall 2005" semester.

I hope that is clear.

edcellgavin

SELECT b.coursename, count(b.coursename) as count
FROM enrollment AS a
INNER JOIN enrollment AS b ON a.studentid = b.studentid
WHERE a.coursename = "CSC001 - abcdef"
AND a.semestername = "Fall 2005"
AND b.coursename != "CSC001 - abcdef"
GROUP BY b.coursename