Home » SQL & PL/SQL » SQL & PL/SQL » Help with Query
Help with Query [message #217480] Fri, 02 February 2007 08:15 Go to next message
lisafitz
Messages: 10
Registered: November 2006
Junior Member
Hi
I was wondering if anyone could help with this query ! At the moment the code that i have is this:

select r.recruit_id, r.description,
r.position, r.position_type, s.emp_stat, count (a.applicant_no)
from
table1 r,
table2 s,
table3 a
where r.recruitment_id in (select r.recruit_id
from table1 r
WHERE r.open_date between '01-Jan-2006' and'31-DEC-2006')
and r.recruit_id = s.recruit_id
and a.applicant_no = s.applicant_no
group by r.recruit_id, r.description,
r.position, r.position_type, a.applicant_no


Currently it is returning a line for every applicant that applied for a job but wot i need it to do is sum the number of applicants for each post

Re: Help with Query [message #217485 is a reply to message #217480] Fri, 02 February 2007 08:43 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
You're group by clause also has the applicant_no in it. You need to take it out of there if that's what you want to count. Also, the s.emp_stat field is not in the group by clause. You'll likely need that there, too.

[Updated on: Fri, 02 February 2007 08:44]

Report message to a moderator

Re: Help with Query [message #217489 is a reply to message #217480] Fri, 02 February 2007 09:04 Go to previous message
lisafitz
Messages: 10
Registered: November 2006
Junior Member
Thank you

That worked!
Previous Topic: problem in procedure
Next Topic: how fetch top 10 row from table
Goto Forum:
  


Current Time: Sat Dec 10 11:03:34 CST 2016

Total time taken to generate the page: 0.07140 seconds