I have a table which has the following contents
Dept actual_month_of_joining Expected_Month_Of_Joining
IT jan09 jan 09
BPO feb 09 jan 09
HR feb 09 jan 09
HR jan 09 jan 09
Now i want to find the actual no.of people joined in a month say jan to the expected no.of people to join in that month(jan) ,grouped by Dept. Can someone please tell me how to do this..
The sql query that i wrote doesnt seem to work :
SELECT s.dept as Department,
case when (count(d.actual_month_of_joining))>= 0 and (count(e.Expected_Month_Of_Joining)) >= 0
then ((count(d.actual_month_of_joining))*100)/(count(e.Expected_Month_Of_Joining)) else
0 end as actual_offer_to_join
SELECT DISTINCT dept
) AS s
JOIN table1 as d
ON d.dept = s.dept
AND d.actual_month_of_joining = 'jan 09'
JOIN table1 as e
ON e.dept = s.dept
and e.Expected_Month_Of_Joining = 'jan 09'
Please help me resolve this.
niths_86, I think this might get you what you want without even doing a join:
, SUM(CASE ACTUAL_MONTH_OF_JOINING WHEN 'JAN 09' THEN 1
END) AS ACTUAL
, SUM(CASE EXPECTEDL_MONTH_OF_JOINING WHEN 'JAN 09' THEN 1
END) AS EXPECTED
GROUP BY DEPT
If it is not exactly what you need (I am not using *100) it might be close enough for you to started.