Home » SQL & PL/SQL » SQL & PL/SQL » Need Help in Grouping
Need Help in Grouping [message #219145] Tue, 13 February 2007 02:55 Go to next message
nksnare
Messages: 9
Registered: November 2006
Location: KL, Malaysia
Junior Member

HI, I need help in Following.

I have a table called Tmp_1

CREATE TABLE Tmp_1 (DebtAge NUMBER,Amount NUMBER);

INSERT INTO Tmp_1 VALUES(1,100);
INSERT INTO Tmp_1 VALUES(2,1100);
INSERT INTO Tmp_1 VALUES(3,2100);
INSERT INTO Tmp_1 VALUES(5,4100);
INSERT INTO Tmp_1 VALUES(7,6100);
INSERT INTO Tmp_1 VALUES(8,7100);
INSERT INTO Tmp_1 VALUES(9,8100);

COMMIT;

I need to get the report which is going to be used in graph so what i am getting by following query is

SELECT Debtage,SUM(amount) FROM Tmp_1 GROUP BY Debtage

DEBTAGE SUM(AMOUNT)
1 100
2 1100
3 2100
5 4100
7 6100
8 7100
9 8100

BUT What i need is following.(I need missing debtage also)
i can't hardcode it becoze debtage can be 1-10, 11-20 ,21-30 etc...

DEBTAGE SUM(AMOUNT)
1 100
2 1100
3 2100
4 0
5 4100
6 0
7 6100
8 7100
9 8100

please helpppppppppppppppppppppp Razz
Re: Need Help in Grouping [message #219160 is a reply to message #219145] Tue, 13 February 2007 07:03 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Set up a table with your debtages in it and outer join from it to the tmp_1 table.
Re: Need Help in Grouping [message #219309 is a reply to message #219160] Tue, 13 February 2007 23:23 Go to previous messageGo to next message
nksnare
Messages: 9
Registered: November 2006
Location: KL, Malaysia
Junior Member

Hi, Thanks for the reply.. I already did the same thing but i just want to know how we can do with the help of oracle inbuild functions.

Regards,
Re: Need Help in Grouping [message #219320 is a reply to message #219309] Wed, 14 February 2007 00:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How would you expect oracle to know which (non-existing) debtages you expect?
If it is simply a matter of filling the gaps between your min and max, then search this board for row generator.
Re: Need Help in Grouping [message #219374 is a reply to message #219320] Wed, 14 February 2007 03:30 Go to previous message
nksnare
Messages: 9
Registered: November 2006
Location: KL, Malaysia
Junior Member

HI, Oracle has a function which generate the rows with incremental numbers in the rowset. The porblem is i don't remember the function name...


Regards,
Previous Topic: sql hard
Next Topic: Primary keys and OIDs
Goto Forum:
  


Current Time: Mon Dec 05 12:42:42 CST 2016

Total time taken to generate the page: 0.09496 seconds