I want formatted output

[Amit Parmar]

HI Everybody,

I have created one table called Employee.this table cantains four columns
ID,NAME,DEPTARTMENT,SALARY.

Now i want out put in below mentioned format.

Emp.ID Emp.Name Dept. Salary Total Salary by Dept
1 X1 Software 5000
2 X2 Software 7000
12000
3 X3 Testing 8000
4 X4 Testing 4000
5 X5 Testing 3000
15000
6 X6 HR 2000
7 X7 HR 1000
3000
Can anyone please help to get output in this format?

Thanks in advance.
amit

[amitpatel66]

HI Everybody,

I have created one table called Employee.this table cantains four columns
ID,NAME,DEPTARTMENT,SALARY.

Now i want out put in below mentioned format.

Emp.ID Emp.Name Dept. Salary Total Salary by Dept
1 X1 Software 5000
2 X2 Software 7000
12000
3 X3 Testing 8000
4 X4 Testing 4000
5 X5 Testing 3000
15000
6 X6 HR 2000
7 X7 HR 1000
3000
Can anyone please help to get output in this format?

Thanks in advance.
amit

 
SQL> ed
Wrote file afiedt.buf
 
  1  select t.id,SUM(salary) from
  2  (select 1 AS ID, 'X1' AS NAME, 'SW' AS DEPT, 1000 AS SALARY from dual
  3  UNION
  4  select 2 AS ID, 'X2' AS NAME, 'SW' AS DEPT, 1000 AS SALARY from dual
  5  UNION
  6  select 3 AS ID, 'X3' AS NAME, 'S' AS DEPT, 1000 AS SALARY from dual
  7  UNION
  8  select 4 AS ID, 'X4' AS NAME, 'S' AS DEPT, 3000 AS SALARY from dual) t
  9* GROUP BY ROLLUP(dept,t.id)
SQL> /
 
        ID SUM(SALARY)
---------- -----------
         3        1000
         4        3000
                  4000
         1        1000
         2        1000
                  2000
                  6000
 
7 rows selected.
 
SQL> 
 
 

[Amit Parmar]

 
SQL> ed
Wrote file afiedt.buf
 
  1  select t.id,SUM(salary) from
  2  (select 1 AS ID, 'X1' AS NAME, 'SW' AS DEPT, 1000 AS SALARY from dual
  3  UNION
  4  select 2 AS ID, 'X2' AS NAME, 'SW' AS DEPT, 1000 AS SALARY from dual
  5  UNION
  6  select 3 AS ID, 'X3' AS NAME, 'S' AS DEPT, 1000 AS SALARY from dual
  7  UNION
  8  select 4 AS ID, 'X4' AS NAME, 'S' AS DEPT, 3000 AS SALARY from dual) t
  9* GROUP BY ROLLUP(dept,t.id)
SQL> /
 
        ID SUM(SALARY)
---------- -----------
         3        1000
         4        3000
                  4000
         1        1000
         2        1000
                  2000
                  6000
 
7 rows selected.
 
SQL> 
 
 

Thanks for your reply,
but, i already got output in this format.
What i want is,"tolal salary by department" should be displayed in separate column. That i have mentioned below.

ID SUM(SALARY) Total Salary by Dept.
---------- ----------- ----------------------------
3 1000
4 3000
------------------------------------4000
1 1000
2 1000
------------------------------------2000
------------------------------------6000

Thanks in advance,
Amit Parmar

[amitpatel66]

Try this:

SQL> ed
Wrote file afiedt.buf
 
  1  select empid,dept,salary ss,NULL from data1
  2  UNION ALL
  3  SELECT NULL,dept,NULL, sum(salary) from data1 group by dept
  4  UNION ALL
  5  SELECT NULL,NULL,NULL,SUM(SALARY) FROM data1
  6* order by dept
SQL> /
 
     EMPID DEPT                         SS       NULL
---------- -------------------- ---------- ----------
         1 SW                        10000
         2 SW                         1000
         5 SW                          100
           SW                                   11100
         3 WWS                       10000
         4 WWS                        1000
           WWS                                  11000
                                                22100
 
8 rows selected.
 
SQL>