SQL Count occurrence of a value

Question

I'm working a course creation application, where the user needs to display all the students in X number of courses. For example, if a biology class is selected, the user should be able to filter users based on how many other courses they are already in. My table has the following fields: id, uid, courseid. I want to do something like but don't know what the syntax should be:

SELECT * FROM course_list WHERE count(uid) > X AND courseid=Z

Where X is the number of courses the user has filtered to (a value 0-3), and Z is simply the id of the course the user is currently looking at.

Justin Niessner · Accepted Answer · 2011-02-01 15:48:02Z

2

You can't use aggregate functions in the where clause of a query. Instead, they belong in the having portion of the query so you would need something like:

select StudentId
from course_list
where CourseId = @CourseId
group by StudentId
having count(uid) > @MaxCount

...or something to that effect.

answered Feb 1, 2011 at 15:48

Justin Niessner

246k40 gold badges416 silver badges546 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dan Over a year ago

Worked perfectly thanks, my only question is what is the @ used for? With how much php i use my knowledge of sql is pretty basic :(

Justin Niessner Over a year ago

@dan - Those would be the parameters to the query. I used them as placeholders instead of X and Z. I'm not sure exactly how MySQL takes parameters.

Jeff · Accepted Answer · 2011-02-01 15:57:24Z

0

Alternatively, you could structure your database thusly:

Table: Students (includes an autoincrementing ID field StudentId)
Table: Cources (includes an autoincrementing ID field CourseId)
Table: StudentCourseAssociation (Includes studentId, courseId, and quarter/semester/year/whatever information)

Then you just:

SELECT StudentId, Count(*) FROM StudentCourseAssociation
WHERE StudentId IN 
(
    SELECT StudentId FROM StudentCourseAssociation WHERE CourseId = @CourseId
)
AND NOT CourseId = @CourseId
GROUP BY StudentId

That should get all students in the course, and give you their ID and the count of other classes they have. My syntax may be incorrect, as I'm typing this from memory.

answered Feb 1, 2011 at 15:57

Jeff

2,8693 gold badges44 silver badges70 bronze badges

1 Comment

Jeff Over a year ago

Also, you can add a HAVING clause as in Justin's answer, if you absolutely need to filter based on the count as well. It would go after the GROUP BY clause.

Collectives™ on Stack Overflow

SQL Count occurrence of a value

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related