SQL subquery counting

[nadeemshafi9]

Hi guys, i am looking for a way using sql whith a subquery to count the amount of properties(pno) each staff member (sno) looks after using 1 table.

here is the schema for the 1 table im talkin about.

property_for_rent (pno, street, area, city, pcode, type, rooms, ono, sno, bno)
PK pno
FK sno
FK bno
FK ono

so i would like to dispplay the sno (sttaff number) along whith the amount (count) of pno (property numbers) that particular staff is repeated in.

i uunderstand count and subqueries, but just cant seem to crack this one.

THANKS FOR NE HELP

[Barand]

Don't double post.

I'll delete your second post on this topic.

I could've been answering your question instead of doing this!

[nadeemshafi9]

sorry i am an old member but now you hav the email notification thing i stopped and ticked that and it got double posted, very sorry wont happen again, plus i had to re register because my old user account had been lost due to ur update, but i like the new fetures.

[mb81]

[!--quoteo(post=358574:date=Mar 26 2006, 01:02 PM:name=nadeemshafi9)--][div class=\'quotetop\']QUOTE(nadeemshafi9 @ Mar 26 2006, 01:02 PM) [snapback]358574[/snapback][/div][div class=\'quotemain\'][!--quotec--]
property_for_rent (pno, street, area, city, pcode, type, rooms, ono, sno, bno)
PK pno
FK sno
FK bno
FK ono

so i would like to dispplay the sno (sttaff number) along whith the amount (count) of pno (property numbers) that particular staff is repeated in.

[/quote]

Was there any reason that this wouldn't work?
[code]
SELECT COUNT(*) AS numproperties, sno FROM property_for_rent GROUP BY sno ORDER BY numproperties
[/code]

[nadeemshafi9]

Thanks, that works perfect, what i am trying to do is study for my first SQL test in 2 months time, now i can analyse and remember how that quesry was put together, i think i need to read up on GROUP BY.

Thanks mb81