Help needed with Joins

niths_86 · #1 (**permalink**) 07-17-09, 04:41

Hi ...
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
FROM (
SELECT DISTINCT dept
FROM table1
) AS s
LEFT OUTER
JOIN table1 as d
ON d.dept = s.dept
AND d.actual_month_of_joining = 'jan 09'
LEFT OUTER
JOIN table1 as e
ON e.dept = s.dept
and e.Expected_Month_Of_Joining = 'jan 09'
GROUP
BY s.Dept;
Please help me resolve this.

Stealth_DBA · #2 (**permalink**) 07-17-09, 08:06

niths_86, I think this might get you what you want without even doing a join:

Code:

SELECT DEPT
     , SUM(CASE ACTUAL_MONTH_OF_JOINING    WHEN 'JAN 09' THEN 1 
                                                         ELSE NULL
           END) AS ACTUAL
     , SUM(CASE EXPECTEDL_MONTH_OF_JOINING WHEN 'JAN 09' THEN 1 
                                                         ELSE NULL
           END) AS EXPECTED
FROM table-name
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.