how to write SQl query for the given problem below with a self join
I was jus wondering how to write an sql query for retrieving the following
Table columns... employee name, ID, employee grade, type of job pursuing.
(The "type of job" field can take one out of 3 predefined values and is non
null)- primary key of the table is employee ID
Question: I want to know the no. of each type of job pursued by employees of
Output required... employee grade, No. of type 1 jobs pursued, no. of type 2
jobs pursued, no. of type 3 jobs pursued.
Hope this will help you.
SELECT distinct grade,
count(decode(job_type, 'Type1', 1))
over(partition by grade) t1,
count(decode(job_type, 'Type2', 1))
over(partition by grade) t2,
count(decode(job_type, 'Type3', 1))
over(partition by grade) t3
ORDER BY 1
Last edited by tabreaz; 07-26-2007 at 06:51 AM.
Click Here to Expand Forum to Full Width