Only Show Results For Certain Count

[paulonline2501]

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

[justsomeguy]

Try to compare number_of_SMRs without the quotes around the number.

[paulonline2501]

hi justSomeGuy,i already tried that - unless 'compare' is a function but i cant find it.try leaving examples and actually testing what you suggest when you leave replys - its much more helpful

Edited November 30, 2009 by as_bold_as_love

[justsomeguy]

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

[paulonline2501]

great!yes, "having count" worked just fine.pauly