Solved

PL/SQL group by question

Posted on 2007-12-06
20
1,043 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
  • 9
  • 5
  • 2
  • +1
20 Comments
 
LVL 73

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 73

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
 
LVL 73

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 73

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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 73

Accepted Solution

by:
sdstuber earned 500 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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now