Skip to Main Content
  • Questions

  • Generate totals based on group while selecting all columns

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Venkat.

Asked: March 03, 2018 - 6:01 pm UTC

Last updated: March 05, 2018 - 3:31 pm UTC

Version: Oracle 11.xx

Viewed 1000+ times

You Asked

Hello-

I need to create an output broken down by Account numbers and Locations.

Here is my sample table: 
Create table sample_dat
(
POSITION_NO number (5), 
NAME varchar2(50),      
TITLE varchar2(50),
ACCOUNT number(15),
GAAP_ACNO number(15),
GAAP_DESC varchar2(50),
FUND varchar2(2),
BUDLOC varchar2(2),
TOT_SAL number(6),
FTE number(4),
STATUS varchar2(15),
LOC varchar2(50)
);


Here is my sample data set: 

insert into sample_dat values(4985,'NAME','TITLE 1',1234,1234,'OTHER SALARIES','11','00',44074,1,'FILLED','01 GEORGE'):
insert into sample_dat values(4982,'NAME','TITLE 1',1234,1234,'OTHER SALARIES','11','00',49253,1,'FILLED','01 GEORGE'):
insert into sample_dat values(4543,'NAME','TITLE 1',1234,1234,'OTHER SALARIES','11','00',44074,1,'FILLED','01 GEORGE'):
insert into sample_dat values(5000,'NAME','TITLE 1',1234,1234,'OTHER SALARIES','11','00',42307,1,'FILLED','01 GEORGE'):
insert into sample_dat values(3113,'NAME ','TITLE 2',12341,12341,'TECH','11','00',0,1,'VACANT','01 GEORGE'):
insert into sample_dat values(1136,'NAME','TITLE 3',12341,12341,'TECH','11','01',9494,1,'FILLED','01 GEORGE'):
insert into sample_dat values(3839,'NAME','TITLE 5',12341,12341,'TECH','11','01',0,1,'VACANT','01 GEORGE'):
insert into sample_dat values(2688,'NAME','TITLE X',2345,2345,'INSTRUCTION','11','00',0,1,'VACANT','02 SCOTT'):
insert into sample_dat values(2724,'NAME','TITLE P',23451,23451,'PP','11','00',0,1,'VACANT','02 SCOTT'):
insert into sample_dat values(4245,'NAME','TITLE Q',23451,23451,'PP','11','00',0,1,'VACANT','02 SCOTT'):
insert into sample_dat values(3362,'NAME','TITLE 1',23451,23451,'PP','11','02',0,1,'FILLED','02 SCOTT'):
insert into sample_dat values(3295,'NAME','TITLE M',23452,23452,'SPORTS','11','02',57479,1,'FILLED','02 SCOTT'):
insert into sample_dat values(2443,'NAME','TITLE M',23452,23452,'SPORTS','11','02',56112,1,'FILLED','02 SCOTT'):
insert into sample_dat values(4144,'NAME','TITLE M',23452,23452,'SPORTS','11','02',56112,1,'FILLED','02 SCOTT'):


Expected output would be as shown: 
POSITION_NO NAME TITLE ACCOUNT GAAP_ACNO GAAP_DESC FUND BUDLOC TOT_SAL FTE STATUS LOC
4985 NAME TITLE 1 1234 1234 OTHER SALARIES 11 00 44074 1 FILLED 01 GEORGE
4982 NAME TITLE 1 1234 1234 OTHER SALARIES 11 00 49253 1 FILLED 01 GEORGE
4543 NAME TITLE 1 1234 1234 OTHER SALARIES 11 00 44074 1 FILLED 01 GEORGE
5000 NAME TITLE 1 1234 1234 OTHER SALARIES 11 00 42307 1 FILLED 01 GEORGE
   Account Total     179708 4  
3113 NAME  TITLE 2 12341 12341 TECH 11 00 0 1 VACANT 01 GEORGE
1136 NAME TITLE 3 12341 12341 TECH 11 01 9494 1 FILLED 01 GEORGE
3839 NAME TITLE 5 12341 12341 TECH 11 01 0 1 VACANT 01 GEORGE
   Account Total     9494 3  
   Location Total     189202 7  
2688 NAME TITLE X 2345 2345 INSTRUCTION 11 00 0 1 VACANT 02 SCOTT
   Account Total     0 1  
2724 NAME TITLE P 23451 23451 PP 11 00 0 1 VACANT 02 SCOTT
4245 NAME TITLE Q 23451 23451 PP 11 00 0 1 VACANT 02 SCOTT
3362 NAME TITLE 1 23451 23451 PP 11 02 0 1 FILLED 02 SCOTT
   Account Total     0 3  
3295 NAME TITLE M 23452 23452 SPORTS 11 02 57479 1 FILLED 02 SCOTT
2443 NAME TITLE M 23452 23452 SPORTS 11 02 56112 1 FILLED 02 SCOTT
4144 NAME TITLE M 23452 23452 SPORTS 11 02 56112 1 FILLED 02 SCOTT
   Account Total     169703 3  
   Location Total     169703 6  
   District Total (Total of All Locations)     358905 13


I would assume this can be achieved using rollup/cube functions. However; not sure how to use these functions. Any
assistance would be appreciated!

Thanks
Venkat

and Chris said...

Every unaggregated column in your select must appear in your group by.

But do this and you're aggregating at the most granular level. If you stick everything in a rollup or cube, you get subtotals for all the columns. You only want these rollups for (account, loc).

You could calculate all the subtotals. Then exclude all those you don't want. The grouping_id (...) function tells you which column you've got the subtotal for:

select POSITION_NO, NAME , TITLE , GAAP_ACNO , GAAP_DESC ,FUND ,BUDLOC , FTE ,STATUS,
       ACCOUNT , LOC, 
       sum(TOT_SAL), count(*),
       grouping_id (
         loc, account, POSITION_NO, NAME , TITLE , GAAP_ACNO , GAAP_DESC ,FUND ,BUDLOC , FTE ,STATUS
       ) gid
from   sample_dat
group  by rollup(
  loc, account, POSITION_NO, NAME , TITLE , GAAP_ACNO , GAAP_DESC ,FUND ,BUDLOC , FTE ,STATUS
);

POSITION_NO   NAME     TITLE     GAAP_ACNO   GAAP_DESC        FUND     BUDLOC   FTE      STATUS   ACCOUNT   LOC         SUM(TOT_SAL)   COUNT(*)   GID    
         2688 NAME     TITLE X          2345 INSTRUCTION      11       00              1 VACANT        2345 02 SCOTT                 0          1      0 
         2688 NAME     TITLE X          2345 INSTRUCTION      11       00              1 <null>        2345 02 SCOTT                 0          1      1 
         2688 NAME     TITLE X          2345 INSTRUCTION      11       00         <null> <null>        2345 02 SCOTT                 0          1      3 
         2688 NAME     TITLE X          2345 INSTRUCTION      11       <null>     <null> <null>        2345 02 SCOTT                 0          1      7 
         2688 NAME     TITLE X          2345 INSTRUCTION      <null>   <null>     <null> <null>        2345 02 SCOTT                 0          1     15 
         2688 NAME     TITLE X          2345 <null>           <null>   <null>     <null> <null>        2345 02 SCOTT                 0          1     31 
         2688 NAME     TITLE X        <null> <null>           <null>   <null>     <null> <null>        2345 02 SCOTT                 0          1     63
...<snip>...


But that's processing more data than you need to.

Luckily there's a way to get specific subgroups:

Grouping sets!

It seems you want four levels:

- All columns
- (Account, location) subtotals
- Location subtotals
- The grand total

The last three are a rollup of:

rollup ( location, account)

So stick this in grouping sets, along with another expression with every unaggregated column. Then use grouping_id to set a "total name" column:

with grps as (
  select case grouping_id (account, loc, POSITION_NO) 
           when 1 then 'Account total'
           when 5 then 'Location total'
           when 7 then 'District total'
         end grp,
         POSITION_NO, NAME , TITLE , GAAP_ACNO , GAAP_DESC ,FUND ,BUDLOC , FTE ,STATUS,
         ACCOUNT , LOC, 
         sum(TOT_SAL), count(*),
         grouping_id (account, loc, POSITION_NO) gid
  from   sample_dat
  group  by grouping sets (
    (POSITION_NO, NAME , TITLE , GAAP_ACNO , GAAP_DESC ,FUND ,BUDLOC , FTE ,STATUS, account, loc),
    rollup(loc, account)
  )
)
  select * from grps
  order  by loc, account, gid;

GRP              POSITION_NO   NAME     TITLE     GAAP_ACNO   GAAP_DESC        FUND     BUDLOC   FTE      STATUS   ACCOUNT   LOC         SUM(TOT_SAL)   COUNT(*)   GID   
<null>                    5000 NAME     TITLE 1          1234 OTHER SALARIES   11       00              1 FILLED        1234 01 GEORGE            42307          1     0 
<null>                    4985 NAME     TITLE 1          1234 OTHER SALARIES   11       00              1 FILLED        1234 01 GEORGE            44074          1     0 
<null>                    4543 NAME     TITLE 1          1234 OTHER SALARIES   11       00              1 FILLED        1234 01 GEORGE            44074          1     0 
<null>                    4982 NAME     TITLE 1          1234 OTHER SALARIES   11       00              1 FILLED        1234 01 GEORGE            49253          1     0 
Account total           <null> <null>   <null>         <null> <null>           <null>   <null>     <null> <null>        1234 01 GEORGE           179708          4     1 
<null>                    3113 NAME     TITLE 2         12341 TECH             11       00              1 VACANT       12341 01 GEORGE                0          1     0 
<null>                    3839 NAME     TITLE 5         12341 TECH             11       01              1 VACANT       12341 01 GEORGE                0          1     0 
<null>                    1136 NAME     TITLE 3         12341 TECH             11       01              1 FILLED       12341 01 GEORGE             9494          1     0 
Account total           <null> <null>   <null>         <null> <null>           <null>   <null>     <null> <null>       12341 01 GEORGE             9494          3     1 
Location total          <null> <null>   <null>         <null> <null>           <null>   <null>     <null> <null>      <null> 01 GEORGE           189202          7     5 
<null>                    2688 NAME     TITLE X          2345 INSTRUCTION      11       00              1 VACANT        2345 02 SCOTT                 0          1     0 
Account total           <null> <null>   <null>         <null> <null>           <null>   <null>     <null> <null>        2345 02 SCOTT                 0          1     1 
<null>                    2724 NAME     TITLE P         23451 PP               11       00              1 VACANT       23451 02 SCOTT                 0          1     0 
<null>                    3362 NAME     TITLE 1         23451 PP               11       02              1 FILLED       23451 02 SCOTT                 0          1     0 
<null>                    4245 NAME     TITLE Q         23451 PP               11       00              1 VACANT       23451 02 SCOTT                 0          1     0 
Account total           <null> <null>   <null>         <null> <null>           <null>   <null>     <null> <null>       23451 02 SCOTT                 0          3     1 
<null>                    3295 NAME     TITLE M         23452 SPORTS           11       02              1 FILLED       23452 02 SCOTT             57479          1     0 
<null>                    4144 NAME     TITLE M         23452 SPORTS           11       02              1 FILLED       23452 02 SCOTT             56112          1     0 
<null>                    2443 NAME     TITLE M         23452 SPORTS           11       02              1 FILLED       23452 02 SCOTT             56112          1     0 
Account total           <null> <null>   <null>         <null> <null>           <null>   <null>     <null> <null>       23452 02 SCOTT            169703          3     1 
Location total          <null> <null>   <null>         <null> <null>           <null>   <null>     <null> <null>      <null> 02 SCOTT            169703          7     5 
District total          <null> <null>   <null>         <null> <null>           <null>   <null>     <null> <null>      <null> <null>              358905         14     7

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you!

Venkat, March 05, 2018 - 2:29 pm UTC

Just perfect!! Will tweak for my custom requirements! Thank you!

Super!!
Chris Saxon
March 05, 2018 - 3:31 pm UTC

Great, glad this helped.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.