?
Solved

PL/SQL group by question

Posted on 2007-12-06
20
Medium Priority
?
1,054 Views
Last Modified: 2013-12-19
I have this PL/SQL statement:
SELECT ROWNUM, acct, amt, item
  FROM (SELECT fprpoda_fund_code||'-'||fprpoda_orgn_code||'-'||fprpoda_acct_code acct,
               (TO_CHAR (  NVL (fprpoda_amt, 0)
                        - NVL (fprpoda_disc_amt, 0)
                        + NVL (fprpoda_tax_amt, 0)
                        + NVL (fprpoda_addl_chrg_amt, 0),
                        '$999,999,999.99'
                       )) amt,
               DECODE (fprpoda_item, '0', NULL, fprpoda_item) item
          FROM fimsmgr.fprpoda
         WHERE fprpoda_pohd_code = RTRIM (LTRIM ('P0006372'))
           AND fprpoda_change_seq_num IS NULL)

I want it to break on each ACCT and list a subtotal      for that ACCT, then produce a grand total for the entire listing.  How can I do this?       
0
Comment
Question by:Jack Seaman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 2
  • +1
20 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 20423463
SELECT   acct, item, amt
    FROM (SELECT   GROUPING(acct) acct_grp, GROUPING(item) item_grp,
                   CASE
                       WHEN GROUPING(acct) = 1
                           THEN '*** Grand Total ***'
                       ELSE acct
                   END acct, CASE
                       WHEN GROUPING(item) = 1 AND GROUPING(acct) = 0
                           THEN '*** Total ***'
                       ELSE item
                   END item, TO_CHAR(SUM(amt), '$999,999,999.99') amt
              FROM (SELECT fprpoda_fund_code || '-' || fprpoda_orgn_code || '-' || fprpoda_acct_code acct,
                             NVL(fprpoda_amt, 0)
                           - NVL(fprpoda_disc_amt, 0)
                           + NVL(fprpoda_tax_amt, 0)
                           + NVL(fprpoda_addl_chrg_amt, 0) amt,
                           DECODE(fprpoda_item, '0', NULL, fprpoda_item) item
                      FROM fprpoda
                     WHERE fprpoda_pohd_code = RTRIM(LTRIM('P0006372')) AND fprpoda_change_seq_num IS NULL)
          GROUP BY ROLLUP(acct, item))
ORDER BY acct_grp, acct, item_grp, item
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20424183
To get your rownum on the result, you simply put the previous query in an inline view

SELECT ROWNUM, acct, item, amt
  FROM (SELECT   acct, item, amt
            FROM (SELECT   GROUPING(acct) acct_grp, GROUPING(item) item_grp,
                           CASE
                               WHEN GROUPING(acct) = 1
                                   THEN '*** Grand Total ***'
                               ELSE acct
                           END acct,
                           CASE
                               WHEN GROUPING(item) = 1 AND GROUPING(acct) = 0
                                   THEN '*** Total ***'
                               ELSE item
                           END item, TO_CHAR(SUM(amt), '$999,999,999.99') amt
                      FROM (SELECT fprpoda_fund_code || '-' || fprpoda_orgn_code || '-'
                                   || fprpoda_acct_code acct,
                                     NVL(fprpoda_amt, 0)
                                   - NVL(fprpoda_disc_amt, 0)
                                   + NVL(fprpoda_tax_amt, 0)
                                   + NVL(fprpoda_addl_chrg_amt, 0) amt,
                                   DECODE(fprpoda_item, '0', NULL, fprpoda_item) item
                              FROM fprpoda
                             WHERE fprpoda_pohd_code = RTRIM(LTRIM('P0006372'))
                               AND fprpoda_change_seq_num IS NULL)
                  GROUP BY ROLLUP(acct, item))
        ORDER BY acct_grp, acct, item_grp, item)
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20425708
You can even try the attached code.

Thanks,


break on acct on report
compute sum label total_amt of amt on acct 
compute sum label grant_total of amt on report
 
SELECT acct, amt, item
  FROM (SELECT fprpoda_fund_code||'-'||fprpoda_orgn_code||'-'||fprpoda_acct_code acct,
               (TO_CHAR (  NVL (fprpoda_amt, 0)
                        - NVL (fprpoda_disc_amt, 0)
                        + NVL (fprpoda_tax_amt, 0)
                        + NVL (fprpoda_addl_chrg_amt, 0),
                        '$999,999,999.99'
                       )) amt,
               DECODE (fprpoda_item, '0', NULL, fprpoda_item) item
          FROM fimsmgr.fprpoda
         WHERE fprpoda_pohd_code = RTRIM (LTRIM ('P0006372'))
           AND fprpoda_change_seq_num IS NULL)
order by acct;

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 74

Expert Comment

by:sdstuber
ID: 20429858
nav_kum_v,

Can you do that in pl/sql?

That looks like sqlplus reporting commands.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20433144
we can do that in pl/sql as well...but we need to write some pl/sql code block.

something like the below :

a) open a cursor to fetch all records required for processing in account order
b) declare variables to hold at acct level and grand total
c) when the cursor for loop iterates, cumulate the total and print.

thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20433990
sorry, that's not what I meant.  

I know how to do it procedurally.
What I meant was, how to apply your answer to a pl/sql question.

Thanks anyway though, sorry for the confusion.
0
 

Author Comment

by:Jack Seaman
ID: 20434629
How can I pull out the "Grand Total" with out changing the 1 to a 2
 WHEN GROUPING(acct) = 2 --Changed from 1 to 2 to suppress grand total
                                   THEN '*** Grand Total ***'
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20438728
If you want everything except the grand total, try this...


SELECT ROWNUM, acct, item, amt
  FROM (SELECT   acct, item, amt
            FROM (SELECT   GROUPING(acct) acct_grp, GROUPING(item) item_grp, acct,
                           CASE
                               WHEN GROUPING(item) = 1 AND GROUPING(acct) = 0
                                   THEN '*** Total ***'
                               ELSE item
                           END item, TO_CHAR(SUM(amt), '$999,999,999.99') amt
                      FROM (SELECT fprpoda_fund_code || '-' || fprpoda_orgn_code || '-'
                                   || fprpoda_acct_code acct,
                                     NVL(fprpoda_amt, 0)
                                   - NVL(fprpoda_disc_amt, 0)
                                   + NVL(fprpoda_tax_amt, 0)
                                   + NVL(fprpoda_addl_chrg_amt, 0) amt,
                                   DECODE(fprpoda_item, '0', NULL, fprpoda_item) item
                              FROM fprpoda
                             WHERE fprpoda_pohd_code = RTRIM(LTRIM('P0006372'))
                               AND fprpoda_change_seq_num IS NULL)
                  GROUP BY ROLLUP(acct, item))
           WHERE acct_grp = 0
        ORDER BY acct_grp, acct, item_grp, item)
0
 
LVL 1

Expert Comment

by:no001855
ID: 20463086
In this case I would have used analytical functions, something like :

select ROWNUM,sq.* FROM (
select distinct acct, amt, item,
                        sum(amt) over (partition by acct) Total,
                       sum(amt) over (partition by 1)      GrandTotal
FROM (SUBQUERY)
) sq;

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20464465
no001855,
that doesn't produce the "break" subtotals as distinct rows,

I'll agree that it would produce the requested totals but they are columns of repeated values.  


0
 
LVL 1

Expert Comment

by:no001855
ID: 20465061
sdstuber
Minor cut/paste error, of course the SQL should not included amt in the outher Subquery:
select ROWNUM,sq.* FROM (
select distinct acct, item,
                        sum(amt) over (partition by acct) Total,
                        sum(amt) over (partition by 1)      GrandTotal
FROM (SUBQUERY)
) sq;
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 20465178
no001855, what I mean was...

the asker is looking for results like this.

A  X 1
A  Y 2
A     3
B X  5
B Y   7
B     12

Your query will yield something like this...

A  X 1   3 15
A  Y 2   3  15
B X  5   12 15
B Y  7   12  15

Your query would sum up the 3 and 12 correctly
but it's not returning the data like the asker requested.








0
 

Author Comment

by:Jack Seaman
ID: 20487807
Thanks for the response.  The user I am working with for the question has to reformat the query for form fusion application.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20487835
wjseaman,  does that mean some of the suggestions helped or do you need further assistance?
0
 

Author Comment

by:Jack Seaman
ID: 20487946
The sugestion on 12.09.2007 at 07:13PM EST, ID: 20438728 is what I am working with now.  I will need further assistance once the user tests it with form fusion.
0
 

Author Comment

by:Jack Seaman
ID: 21441683
Please close question.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 21441902
you have to close it,  pick the answer (or answers if more than one) that helped you, give a grade and divide the points up
0
 

Author Comment

by:Jack Seaman
ID: 21728733
Closing the question
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question