I want formatted output

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Amit Parmar
    New Member
    • Jan 2008
    • 4
    #1

    I want formatted output

    HI Everybody,

    I have created one table called Employee.this table cantains four columns
    ID,NAME,DEPTART MENT,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
    Tags: None
    • amitpatel66
      Recognized Expert Top Contributor
      • Mar 2007
      • 2358
      #2
      Originally posted by Amit Parmar
      HI Everybody,

      I have created one table called Employee.this table cantains four columns
      ID,NAME,DEPTART MENT,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
      [code=oracle]

      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.i d)
      SQL> /

      ID SUM(SALARY)
      ---------- -----------
      3 1000
      4 3000
      4000
      1 1000
      2 1000
      2000
      6000

      7 rows selected.

      SQL>

      [/code]

        Comment

        • Amit Parmar
          New Member
          • Jan 2008
          • 4
          #3
          Originally posted by amitpatel66
          [code=oracle]

          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.i d)
          SQL> /

          ID SUM(SALARY)
          ---------- -----------
          3 1000
          4 3000
          4000
          1 1000
          2 1000
          2000
          6000

          7 rows selected.

          SQL>

          [/code]
          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

            Comment

            • amitpatel66
              Recognized Expert Top Contributor
              • Mar 2007
              • 2358
              #4
              Try this:

              [code=oracle]
              SQL> ed
              Wrote file afiedt.buf

              1 select empid,dept,sala ry 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>
              [/code]

                Comment

                Previous template Next
                Working...