Jump to content


Photo

SQL subquery counting


  • Please log in to reply
4 replies to this topic

#1 nadeemshafi9

nadeemshafi9
  • Members
  • PipPipPip
  • Advanced Member
  • 1,245 posts
  • LocationUK

Posted 26 March 2006 - 06:02 PM

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

DONT LAY AN EGG OVER IT

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 26 March 2006 - 06:34 PM

Don't double post.

I'll delete your second post on this topic.

I could've been answering your question instead of doing this!
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 nadeemshafi9

nadeemshafi9
  • Members
  • PipPipPip
  • Advanced Member
  • 1,245 posts
  • LocationUK

Posted 26 March 2006 - 06:38 PM

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.
DONT LAY AN EGG OVER IT

#4 mb81

mb81
  • Members
  • PipPipPip
  • Advanced Member
  • 120 posts

Posted 26 March 2006 - 07:20 PM

[!--quoteo(post=358574:date=Mar 26 2006, 01:02 PM:name=nadeemshafi9)--][div class=\'quotetop\']QUOTE(nadeemshafi9 @ Mar 26 2006, 01:02 PM) View Post[/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?
SELECT COUNT(*) AS numproperties, sno FROM property_for_rent GROUP BY sno ORDER BY numproperties




#5 nadeemshafi9

nadeemshafi9
  • Members
  • PipPipPip
  • Advanced Member
  • 1,245 posts
  • LocationUK

Posted 26 March 2006 - 09:38 PM

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
DONT LAY AN EGG OVER IT




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users