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.

4 total posts (Page 1 of 1)  
| 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

Back to Web Development Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums