Forum Stats

  • 3,874,989 Users
  • 2,266,791 Discussions
  • 7,912,045 Comments

Discussions

Categories

Pivot with multiple aggregate function calls?

TexasApexDeveloper
TexasApexDeveloper Member Posts: 7,995 Gold Crown
edited Dec 9, 2014 8:20AM in SQL & PL/SQL

Hi,

  Using Oracle 11g R2, with APEX 4.2.3, I am trying to take the following cross tab (pivot) built query:

begin
return 'with pivot_data as (
                                                SELECT department_id, job_id, salary
                                                FROM oehr_employees
                                              )
SELECT *
FROM pivot_data
PIVOT (
SUM(salary)
FOR department_id
IN ('||v('P5_DEPT_LIST')||')
)';
end;

Which works just fine, and add in the comm column and also have the pivot sum and build department_id based columns..  I haven't found any documentation showing how to have multiple columns summed in a pivot query yet.

Any ideas??

Thank you,

Tony Miller
LuvMuffin Software
Ruckersville, VA

Tagged:
· Share on TwitterShare on Facebook

Best Answer

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,766 Red Diamond
    edited Dec 8, 2014 10:33PM

    Hi,

    It's not clear what you want.  It would help if you posted a little sample data (CREATE TABLE and INSERT statements) and the exact results you want from that sample data.

    If you want to SUM, or do any kind of aggregate function, on multiple columns, here's one way:

    WITH    pivot_data    AS
(
    SELECT  job, sal, comm, deptno
    FROM    scott.emp
)
SELECT    *
FROM      pivot_data
PIVOT     (    SUM (sal)      AS sal
          ,    SUM (comm)     AS comm
          ,    AVG (sal)      AS avgsal
          FOR  job  IN ( 'ANALYST'    AS analyst
                       , 'CLERK'      AS clerk
                       , 'MANAGER'    AS mannager
                       , 'PRESIDENT'  AS president
                       , 'SALESMAN'   AS salesman
                       )
          )
ORDER BY  deptno
;

    See the Forum FAQ:

    · Share on TwitterShare on Facebook
  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    Answer ✓ 
    TexasApexDeveloper wrote:

I haven't found any documentation showing how to have multiple columns summed in a pivot query yet.

    Not even THE official documentation?

    http://docs.oracle.com/cd/E11882_01/server.112/e25554/analysis.htm#DWHSG8731

    · Share on TwitterShare on Facebook
  • TexasApexDeveloper
    TexasApexDeveloper Member Posts: 7,995 Gold Crown

    My bad, I should have done further research.. That looks spot on!!

    Thank you,

    Tony Miller
    LuvMuffin Software
    Ruckersville, VA

    · Share on TwitterShare on Facebook
This discussion has been closed.