geet16
New User
Joined: 22 Dec 2006
Posts: 24
Location: USA
|
|
|
|
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 |
|
Bhagyalakshmi C
New User
Joined: 24 Jul 2007
Posts: 38
Location: Bangalore
|
|
|
|
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!! |
|
