Home » SQL & PL/SQL » SQL & PL/SQL » Subtotal & Grand Total at the last in output.
Subtotal & Grand Total at the last in output. [message #203998] Fri, 17 November 2006 05:04 Go to next message
rohgupte
Messages: 7
Registered: November 2006
Location: Bangalore
Junior Member

Hi friends...
I need subtotals after each type of payment category and the GrandTotal at the last. The sample data 'current' and the required is shown in the attached excel file.

What changes i need to do in the sql below. Pls suggest / help.

select "Payment/Reversal Type","Post Date",
sum("# of Payments/Reversals") as "# of Payments/Reversals",
sum("Payment/Reversal Amount") as "Payment/Reversal Amount"
from v_PAYMENT_SUMMARY_REPORT
group by "Payment/Reversal Type", "Post Date" ;

How can I use the Rollup function, i checked it but its not coming properly. Pls suggest.

Thanks
Rohan
Re: Subtotal & Grand Total at the last in output. [message #204008 is a reply to message #203998] Fri, 17 November 2006 05:31 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Instead of attaching an Excel file (which I will not download from an unknown source), you should have posted CREATE TABLE and INSERT sample data statements; having (I presume) list of data still requires people to construct their own CREATE TABLE and INSERT statements, and not everyone is willing to do so.

Therefore, here is an example based on Scott's schema. Check it and apply to your problem:
SQL> break on report on deptno
SQL> compute sum of sal on report
SQL> compute sum of sal on deptno
SQL>
SQL> select deptno, ename, sal from emp order by 1;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
           KING             5000
           MILLER           1300
**********            ----------
sum                         8750
        20 JONES            2975
           FORD             3000
           ADAMS           99999
           SMITH             800
           SCOTT            3000
**********            ----------
sum                       109774
        30 BURGLAR          1250
           TURNER           1500
           ALLEN            1600
           JAMES             950
           BLAKE            2850
**********            ----------
sum                         8150
        50 TIGER            1250
**********            ----------
sum                         1250
                      ----------
sum                       127924

14 rows selected.

SQL>
Re: Subtotal & Grand Total at the last in output. [message #204177 is a reply to message #204008] Sat, 18 November 2006 10:34 Go to previous messageGo to next message
rohgupte
Messages: 7
Registered: November 2006
Location: Bangalore
Junior Member

As you said U cannot download the file...here is the requirement. The SQL is the same as shown in prev post.

Current report -

Payment Source Post Date # of Payments/Reversals Payment/Reversal Amount
-------------------------------------------------------------------------------
BIP 10/6/2006 0:00 200 36036.86
BIP 10/7/2006 0:00 102 3298.33
BIP 10/9/2006 0:00 117 6435.27
POS LBX 10/1/2006 0:00 161 12160.36
POS LBX 10/2/2006 0:00 2484 201825.77
POS LBX 10/3/2006 0:00 2446 202071.05
FHB Manual 10/4/2006 0:00 659 195068.29
FHB Manual 10/5/2006 0:00 390 259607.01
FHB Manual 10/6/2006 0:00 370 239982.52

Need the final report to be -

Payment Source Post Date # of Payments/Reversals Payment/Reversal Amount
-------------------------------------------------------------------------------
BIP 10/6/2006 0:00 200 36036.86
BIP 10/7/2006 0:00 102 3298.33
BIP 10/9/2006 0:00 117 6435.27

BIP SubTotal 419 45770.46

POS LBX 10/1/2006 0:00 161 12160.36
POS LBX 10/2/2006 0:00 2484 201825.77
POS LBX 10/3/2006 0:00 2446 202071.05

POS LBX SubTotal 5091 416057.18

FHB Manual 10/4/2006 0:00 659 195068.29
FHB Manual 10/5/2006 0:00 390 259607.01
FHB Manual 10/6/2006 0:00 370 239982.52

FHB Manual SubTotal 1419 694657.82
Grand Total 6929 1156485.46
Re: Subtotal & Grand Total at the last in output. [message #204193 is a reply to message #204177] Sat, 18 November 2006 13:21 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, so did you apply my sample query to your sample data? What did you get as a result? Could you post the whole SQL*Plus session so that we can see it?
Re: Subtotal & Grand Total at the last in output. [message #204208 is a reply to message #204193] Sun, 19 November 2006 00:26 Go to previous messageGo to next message
rohgupte
Messages: 7
Registered: November 2006
Location: Bangalore
Junior Member

Yes I applied, and it took around 40mins to give the result. The result was very hepizard...somewhere 8000 rows it has given that i cannot put here...and no sum was calculated.

One main thing i did not understand that wat does it mean...
SQL> break on report on deptno

I am using TODD and I had to execute your statements as script. then it do executed but the final select statement it has given around 8000 rows while the actual rows in the view is only 3000.
Re: Subtotal & Grand Total at the last in output. [message #204226 is a reply to message #204208] Sun, 19 November 2006 05:27 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd say that you must have not correctly joined those tables if you got 8000 instead of 3000 records.

Don't run such a query in TOAD becuase you will NOT see any sum; BREAK, COMPUTE etc. are SQL*Plus commands and TOAD does not understand them.

To learn how to use such things, read SQL*Plus User's Guide and Reference (here is a link to SQL*Plus Command Summary page).
Re: Subtotal & Grand Total at the last in output. [message #204322 is a reply to message #204226] Mon, 20 November 2006 02:56 Go to previous messageGo to next message
romi
Messages: 67
Registered: October 2006
Member

We can also use ROllup as:-


select deptno,ename sal
from emp
group by rollup(deptno,ename sal);


Try it.
Re: Subtotal & Grand Total at the last in output. [message #204337 is a reply to message #204322] Mon, 20 November 2006 03:41 Go to previous messageGo to next message
amritava
Messages: 13
Registered: September 2006
Location: Kolkata
Junior Member
Yes.....we can use ROLLUP as well as CUBE also to get the subtotal and grand total..
Regards
Amritava
Re: Subtotal & Grand Total at the last in output. [message #204402 is a reply to message #203998] Mon, 20 November 2006 08:43 Go to previous messageGo to next message
rohgupte
Messages: 7
Registered: November 2006
Location: Bangalore
Junior Member

Thanks all...I got the desired o/p thru rollup and grouping.
icon14.gif  Re: Subtotal & Grand Total at the last in output. [message #204403 is a reply to message #204226] Mon, 20 November 2006 08:45 Go to previous message
rohgupte
Messages: 7
Registered: November 2006
Location: Bangalore
Junior Member

But i am having single view only, if you'd have seen the sql in my very first post. No two tables are involved.Anyway...thanks a ton buddy.. got to know one more way to break the report... Thanks again.

[Updated on: Mon, 20 November 2006 08:48]

Report message to a moderator

Previous Topic: Total newbie to PL/SQL should be an easy one for you gurus.....
Next Topic: Crypt and Decrypt Fileds
Goto Forum:
  


Current Time: Wed Dec 07 02:54:27 CST 2016

Total time taken to generate the page: 0.25233 seconds