Web Development

Our forums are currently in maintenance mode and the ability to post is disabled. We will be back up and running as soon as possible. Thanks for your patience!

General discussion

Locked

SQL retrieving only rows of a size

By john.a.wills ·
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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by j.lupo In reply to SQL retrieving only rows ...

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

Collapse -

by john.a.wills In reply to

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.

Collapse -

by john.a.wills In reply to SQL retrieving only rows ...

The solution is to give the outer shrtckn an alias and use that alias in the nested SELECT.

Collapse -

by john.a.wills In reply to SQL retrieving only rows ...

This question was closed by the author

Related Discussions

Related Forums