Oracle 11g Pivot aggrgate sum column

talahi
talahi used Ask the Experts™
on
Using this example below of a simple pivot query, I'm trying to also add a 4th column that sums the previous 3 columns.  So the result looks like this,

CLUSTER  INDEX TABLE SUM
10             468     374      852

select * from (
   select object_type
   from dba_objects
)
pivot
(
   count(object_type)
   for object_type in ('CLUSTER', 'INDEX', 'TABLE')
);
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
select x.* , clus + ind + tab as sum
 from
(select * from (
   select object_type
   from dba_objects
)
pivot
(
   count(object_type)
   for object_type in ('CLUSTER' as clus, 'INDEX' as ind, 'TABLE' as tab)
)) x;


just need to alias the pivot results
Most Valuable Expert 2011
Top Expert 2012

Commented:
or, you can use the default aliases, but then you'll need to put them in double quotes


SELECT x.*, "'CLUSTER'" + "'INDEX'" + "'TABLE'" AS SUM
  FROM (SELECT *
          FROM (SELECT object_type FROM dba_objects) PIVOT (COUNT(object_type)
                                                     FOR object_type
                                                     IN ('CLUSTER', 'INDEX', 'TABLE'))) x;

Author

Commented:
Thanks again.  I really appreciate your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial