Jump to content

Only Show Results For Certain Count


paulonline2501
 Share

Recommended Posts

hi there,im working in ORACLE.what i want to do is count the number of rows in one table i have for each user that meet certain criteria.so i would return their id plus a count of the rows they have.i can do that.i then want to only show if there is a count of 3.can i do something like "and count(*) = 3"? it doesnt seem to work but kind find syntax.EXAMPLE CODE:select smr.spr_code as SPR, count(1) as number_of_SMRsfrom (ins_stu stu left join ins_smr smr on stu.stu_code = substr(smr.spr_code,1,8)) left outer join ins_spr spr on stu.stu_code = substr(spr.spr_code,1,8)where spr.SPR_AYRS = '2008/9'and smr.mod_code like 'SSCS%'and smr.ayr_code = '2008/9'--and number_of_SMRs = '3'--and count(smr.spr_code) = 3group by smr.spr_codeorder by number_of_SMRs descEXAMPLE OUTPUT12523019/1 312524087/1 312521542/1 312521237/1 312527164/1 212521565/1 2

Link to comment
Share on other sites

I don't have an Oracle installation to test anything on, I don't do Oracle development. I'm making suggestions based on the other DBMSs I've used.Check if Oracle has a HAVING clause. If so, that's probably the one you need to use, e.g.:group by smr.spr_code HAVING COUNT(smr.spr_code) = 3

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...