Web Development

Ask something
Discussion Locked

SQL retrieving only rows of a size

0 helpful
4 replies

by john.a.wills

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I want to examine the grades of students only if there are at least 10 students in the class. I think I should be able to write something like this, but I get all classes. What am I doing wrong?
select
shrtckn_subj_code,
shrtckn_crse_numb,
shrtckg_grde_code_final
FROM shrtckn LEFT JOIN shrtckg ON shrtckg_pidm = shrtckn_pidm
WHERE (SELECT count(b.shrtckn_pidm) FROM shrtckn b
WHERE b.shrtckn_crn = shrtckn_crn) > 9

4 replies

by j.lupo

First this looks like the BANNER Student system? am I correct?

Next you might try the HAVING clause. Having Count(shrtckn_pidm) > 9

you could also do
WHERE 9 > (SELECT count(b.shrtckn_pidm)
FROM shrtckn b WHERE ...

I find with sub-selects you cannot make them part of your WHERE clause so easily. It is better to make your WHERE clause actually comapre someing

by john.a.wills

1. Banner it is - as also for my SQR question adjacent to this one.
2. I tried 9> instead of <9 and got the same result.
3. How should I group for HAVING? I need to get every grade of the classes selected.
4. There may be something wrong with my nested SELECT: when I include it in the SELECT list I get a colossal number, probably the size of the table.

This discussion is closed to new replies.