nadeemshafi9 Posted March 26, 2006 Share Posted March 26, 2006 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 pnoFK snoFK bnoFK onoso 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted March 26, 2006 Share Posted March 26, 2006 Don't double post.I'll delete your second post on this topic.I could've been answering your question instead of doing this! Quote Link to comment Share on other sites More sharing options...
nadeemshafi9 Posted March 26, 2006 Author Share Posted March 26, 2006 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. Quote Link to comment Share on other sites More sharing options...
mb81 Posted March 26, 2006 Share Posted March 26, 2006 [!--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 pnoFK snoFK bnoFK onoso 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] Quote Link to comment Share on other sites More sharing options...
nadeemshafi9 Posted March 26, 2006 Author Share Posted March 26, 2006 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.