Jump to content

Archived

This topic is now archived and is closed to further replies.

nadeemshafi9

SQL subquery counting

Recommended Posts

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

Share this post


Link to post
Share on other sites
Don't double post.

I'll delete your second post on this topic.

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[!--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]


Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.