By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,538 Members | 869 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,538 IT Pros & Developers. It's quick & easy.

Need help with query

P: n/a
I have two tables (classes and students). Right now, I'm generating a
report in asp that shows a list of classes, the enrollment for each
class, and how many seats are available (see query below).

SELECT classes.classid, classes.classname, classes.maxstudents,
classes.Active COUNT(students.class) AS countstudentsclass,
classes.maxstudents - COUNT(students.class) AS remain FROM classes LEFT
OUTER JOIN students ON students.class = classes.classid GROUP BY
classes.classid, classes.classname, classes.maxstudents ORDER BY
classes.classid

Now, I want to filter out any class that has an Active value that = 0.
I've tried inserting WHERE classes.Active <> 0 in multiple places, but
I keep getting sytax error messages.

Can anyone help me figure this out? I'm new to SQL so please forgive
me.

Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Stu
Not sure if this is an issue or not, but you're missing a comma in your
list (between the column Active and the agg function COUNT). You are
also missing the Active column in your GROUP BY clause.

SELECT classes.classid, classes.classname, classes.maxstudents,
classes.Active,
COUNT(students.class) AS countstudentsclass,
classes.maxstudents - COUNT(students.class) AS remain
FROM classes LEFT OUTER JOIN students ON students.class =
classes.classid
WHERE classes.active <>0
GROUP BY
classes.classid, classes.classname, classes.maxstudents, classes.Active
ORDER BY
classes.classid

HTH,
Stu

Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.