Home » SQL & PL/SQL » SQL & PL/SQL » HOW TO DISPLAY THE SUMMARY LINE ONLY
HOW TO DISPLAY THE SUMMARY LINE ONLY [message #191723] Thu, 07 September 2006 23:03 Go to next message
NGSOOKFONG
Messages: 16
Registered: August 2006
Junior Member

Dear Sir

Below is my SQL scripts,Please help to check.


SELECT PART, QTY,
SUM(QTY)OVER(PARTITION BY PART ORDER BY PART)BALANCE,
DATE_TRAN
FROM TABLE_ABC


OUTPUT :
--------------

ABC 4 4 10-Jul-04
ABC -2 2 12-JUL-04
ABC 8 10 30-JUL-04
ABC 9 19 13-AUG-04
ABC -8 11 27-AUG-04
XYZ 9 9 10-JUL-04
XYZ 2 11 28-JUL-04
XYZ 10 21 30-AUG-04



THE OUTPUT THAT I WANT IS
--------------------------

ABC 8 10 30-JUL-04
ABC -8 11 27-AUG-04
XYZ 2 11 28-JUL-04
XYZ 10 21 30-AUG-04


Pls help to correct my SQL scripts. Thanks Mabel


Re: HOW TO DISPLAY THE SUMMARY LINE ONLY [message #191734 is a reply to message #191723] Fri, 08 September 2006 00:11 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

Hi

Try this modified query...

SELECT PART, QTY, 
       SUM(QTY)OVER(PARTITION BY PART ORDER BY PART)BALANCE,
       DATE_TRAN
FROM   TABLE_ABC
WHERE  DATE_TRAN IN ( SELECT MAX(DATE_TRAN) 
	  	      FROM   TABLE_ABC 
		      GROUP BY PART, TO_CHAR(DATE_TRAN,'MON'));



Thks & Rgds
Venkat

[Updated on: Fri, 08 September 2006 04:01]

Report message to a moderator

Re: HOW TO DISPLAY THE SUMMARY LINE ONLY [message #191790 is a reply to message #191734] Fri, 08 September 2006 02:54 Go to previous messageGo to next message
NGSOOKFONG
Messages: 16
Registered: August 2006
Junior Member

Dear Sir,

I had try your script, but still cannot get the correct output.

Actually i need a summary of each month for each part.

thanks

mabel
Re: HOW TO DISPLAY THE SUMMARY LINE ONLY [message #191792 is a reply to message #191790] Fri, 08 September 2006 03:02 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Please supply create table scripts and insert scripts and format your code by putting your code in code tags thus:
[code] your code here [/code]

Thanks
Jim
Re: HOW TO DISPLAY THE SUMMARY LINE ONLY [message #191821 is a reply to message #191723] Fri, 08 September 2006 04:35 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

Hi

I am sorry for the wrong query, and here is the new query that works for you...

CREATE TABLE ABC 
( 
  PART 		 VARCHAR2(3),
  DATE_TRAN	 DATE,
  QTY		 NUMBER(5)
);

INSERT INTO ABC (PART, QTY, DATE_TRAN) 
VALUES		('ABC', 4, TO_DATE('10-JUL-04','DD-MON-YY'));

INSERT INTO ABC (PART, QTY, DATE_TRAN) 
VALUES		('ABC', -2, TO_DATE('12-JUL-04','DD-MON-YY'));

INSERT INTO ABC (PART, QTY, DATE_TRAN) 
VALUES		('ABC', 8, TO_DATE('30-JUL-04','DD-MON-YY'));

INSERT INTO ABC (PART, QTY, DATE_TRAN)
VALUES		('ABC', 9, TO_DATE('13-AUG-04','DD-MON-YY')); 

INSERT INTO ABC (PART, QTY, DATE_TRAN)
VALUES		('ABC', -8, TO_DATE('27-AUG-04','DD-MON-YY'));

INSERT INTO ABC (PART, QTY, DATE_TRAN)
VALUES		('XYZ', 9, TO_DATE('10-JUL-04','DD-MON-YY'));

INSERT INTO ABC (PART, QTY, DATE_TRAN)
VALUES		('XYZ', 2, TO_DATE('28-JUL-04','DD-MON-YY'));

INSERT INTO ABC (PART, QTY, DATE_TRAN)
VALUES		('XYZ', 10, TO_DATE('30-AUG-04','DD-MON-YY'));

COMMIT;

SQL> SELECT * 
  2  FROM   ABC;

PAR DATE_TRAN        QTY NEEDDATE
--- --------- ---------- ---------
ABC 10-JUL-04          4 08-SEP-06
ABC 12-JUL-04         -2 08-SEP-06
ABC 30-JUL-04          8 08-SEP-06
ABC 13-AUG-04          9 08-SEP-06
ABC 27-AUG-04         -8 08-SEP-06
XYZ 10-JUL-04          9 08-SEP-06
XYZ 28-JUL-04          2 08-SEP-06
XYZ 30-AUG-04         10 08-SEP-06

8 rows selected.

Now here is the main query for you:

SELECT PART, QTY, 
       ( SELECT SUM(a2.qty) 
	 FROM 	ABC a2 
	 WHERE  a2.part = a1.part 
	 AND 	a2.ROWID <= a1.ROWID
       ) BALANCE,
       DATE_TRAN 
FROM   ABC a1
WHERE  DATE_TRAN IN ( SELECT MAX(DATE_TRAN) 
	  	      FROM   ABC 
		      GROUP BY PART, TO_CHAR(DATE_TRAN,'MON'))
ORDER BY PART;

PART        QTY    BALANCE DATE_TRAN
---- ---------- ---------- ---------
ABC          8         10 30-JUL-04
ABC         -8         11 27-AUG-04
XYZ          2         11 28-JUL-04
XYZ         10         21 30-AUG-04




Thks & Rgds
Venkat.

[Updated on: Fri, 08 September 2006 04:43]

Report message to a moderator

Re: HOW TO DISPLAY THE SUMMARY LINE ONLY [message #192086 is a reply to message #191821] Sun, 10 September 2006 21:19 Go to previous message
NGSOOKFONG
Messages: 16
Registered: August 2006
Junior Member

HI VENKET,


THANKS FOR YOUR HELP.

MABEL
Previous Topic: Stored Procedure using SQL insert for clob data > 4k
Next Topic: Regarding Closing of a cursor.
Goto Forum:
  


Current Time: Fri Dec 09 05:37:17 CST 2016

Total time taken to generate the page: 0.08489 seconds