Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need to print sub Totals

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
geet16

New User


Joined: 22 Dec 2006
Posts: 24
Location: USA

PostPosted: Mon Sep 03, 2007 3:46 pm    Post subject: Need to print sub Totals
Reply with quote

Hi,

We are working on a piece of query in DB2 wherein we need to print individual values as well subtotal.

For eg:
Pay No Inv No Amount
x x1 200
x x2 300
x x3 400
Total Amount for x is 900
y y1 100
total amount for y is 100

I tried using Group By Rollup and Group By Cube but i am getting a error SQLCODE=-104.

Please let me know if there is any other way for printing the same.

Regards,
Gayathri
Back to top
View user's profile Send private message

stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Sep 03, 2007 8:11 pm    Post subject:
Reply with quote

You can't get this in a single query. SQL was not designed to generate reports like this.
Back to top
View user's profile Send private message
Bhagyalakshmi C

New User


Joined: 24 Jul 2007
Posts: 38
Location: Bangalore

PostPosted: Tue Sep 04, 2007 12:43 pm    Post subject:
Reply with quote

Hi,

Hope this is ur requirement.
Check it out!!!


CREATE TABLE NEW1(PAY_NO CHAR(1),INV_NO CHAR(2),AMOUNT INTEGER)


INSERT INTO NEW1 VALUES('x','x1',100)
INSERT INTO NEW1 VALUES('x','x2',200)
INSERT INTO NEW1 VALUES('x','x3',300)

INSERT INTO NEW1 VALUES('y','y1',100)
INSERT INTO NEW1 VALUES('y','x2',300)

INSERT INTO NEW1 VALUES('z','z1',300)
INSERT INTO NEW1 VALUES('z','z2',500)

SELECT * FROM NEW1
-----------------

PAY INV
NO NO AMOUNT
--- --- -----------
X X1 100
X X2 200
X X3 300
Y Y1 100
Y Y2 300
Z Z1 300
Z Z2 500
-----------------------------------------

SELECT PAY_NO,SUM(AMOUNT) AS AMOUNT FROM NEW1 GROUP BY PAY_NO


PAY
NO AMOUNT
--- -----------
X 600
Y 400
Z 800


Please correct if i am wrong!!

Thank you!!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts print out the correct info in LOOP? jackzhang75 CLIST & REXX 7 Wed Dec 23, 2015 10:39 pm
No new posts How to print a variable in SAS progra... LOKESH DAIVAM All Other Mainframe Topics 6 Thu Aug 13, 2015 12:24 pm
No new posts count totals anatol DFSORT/ICETOOL 7 Mon Aug 25, 2014 11:41 pm
No new posts How to print out only array member wi... jackzhang75 CLIST & REXX 13 Wed Jun 18, 2014 7:15 pm
No new posts RMDS - Print table using DBNMALST Mark Kerner IBM Tools 2 Thu May 15, 2014 8:38 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us