Another Challenging Query -- Typical Format Of Output Reqd.

[UDIT]

Hi again ,

This time another query for you , another challenging one.
I ve used the Emp table which is normally there in every oracle edition. ( Emp table has columns > empno,ename,sal,deptno etc)

require format needs to contain the Maximum, minimum and the average salaries of dept 10,20 and 30.

The following output is required:
Dept 10 Dept 20 Dept 30
---------- ---------- ----------
5000 3000 2850 <---Max Sal
.. .. .. <---Min sal
.. .. .. <---Avg Sal


But i have been able to generate only the MAX sal , i am not able to figure out how to generate the minimun salary and the average salary.

Query I used:
select max(decode(deptno,10,sal)) &quot;Dept 10&quot;,max(decode(deptno,20,sal)) &quot;Dept 20&quot;,max(decode(deptno,30,sal)) &quot;Dept 30&quot; from emp;
My Output :

Dept 10 Dept 20 Dept 30
---------- ---------- ----------
5000 3000 2850

please help,
Udit.

 

[javaarray]

Here is the code for your problem:

SELECT job, ROUND(MAX(sal),0) &quot;Maximum&quot;,
ROUND(MIN(sal),0) &quot;Minimum&quot;,
ROUND(SUM(sal),0) &quot;Sum&quot;,
ROUND(AVG(sal),0) &quot;Average&quot;
FROM emp
GROUP BY deptno;

This should put you about were you need to be.

 

[UDIT]

hi javaarray ,
thanks , but your SQL query does not generate the output in the way i require , ( PLEASE NOTE AGAIN THE FORMAT I,VE MENTIONED) . Please check it out again.

Udit.

 

[Guest_imported]

Try:

select max(decode(deptno,10,sal)) &quot;Dept 10&quot;,max(decode(deptno,20,sal)) &quot;Dept 20&quot;,max(decode(deptno,30,sal)) &quot;Dept 30&quot; from emp
union all
select min(decode(deptno,10,sal)) &quot;Dept 10&quot;,min(decode(deptno,20,sal)) &quot;Dept 20&quot;,min(decode(deptno,30,sal)) &quot;Dept 30&quot; from emp
union all
select avg(decode(deptno,10,sal)) &quot;Dept 10&quot;,avg(decode(deptno,20,sal)) &quot;Dept 20&quot;,avg(decode(deptno,30,sal)) &quot;Dept 30&quot; from emp;

 

[ljspop]

If this is just a brain teaser and you are looking
for a single sql step - I don't know.

If this is an actual problem you are facing one solution
would be to create a temporary table with number columns
representing each of the department numbers. The table
could then be filled with rows representing sum, avg etc.
(A script should be set up to create the temp table on the
fly in case departments are added.)

A simple select * from your table would return the data in
your desired format.

If it is brain teaser, I'd be curious to see a solution with
simple SQL.

Bob

 

[UDIT]

thanks a lot Guys ,
i finally got the solution , i wonder why did the UNION ALL clause never struck me , but Thanks again.