July 28, 2008 at 11:30 pm
I have two columns in a table. First column as of Date type and another one is of number type.First column is for storing the Bill dates and second one for storing the Bill amount. I am using Oracle 9i.
Table A
d--> date, a--> number type
When I execute below query :--
Select decode(to_char(d,'mm'),1,'JAN',2,'FEB',3,'MAR',4,'APR',5,'MAY',6,'JUN',7,'JUL',8,'AUG',9,'SEP',10,'OCT',11,'NOV',12,'DEC')) month, sum(a) amount,from A where to_char(d,'YYYY')=2008 group by to_char(d,'MM') order by to_char(d,'MM')
it works fine. But it gives only those months, in which any bill has been submitted.
out put is like:-
MONTH AMOUNT
JAN 123
FEB 2323
MAR 23
APR 45
JUN 89
DEC 345
But i want o/p like below :-
MONTH AMOUNT
JAN 123
FEB 2323
MAR 23
APR 45
MAY 0
JUN 89
JUL 0
AUG 0
SEP 0
OCT 0
NOV 0
DEC 345
can anyone help me??
Thanks in advance
July 29, 2008 at 3:29 am
it's urgent... if you are not getting my problem then let me know...
July 29, 2008 at 10:07 pm
please help me....
July 29, 2008 at 10:39 pm
Try using isnull(sum(a),0) amount.
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
July 29, 2008 at 11:23 pm
I think you are not getting my problem. My problem is that if i am not submitting bill in May.Then you group data with month. Then May will not come into picture as you can see my o/p metioned above. But i want 'May' month also in my o/p having amount 0.
July 30, 2008 at 12:12 am
select decode(t.month,1,'JAN',2,'FEB',3,'MAR',4,'APR',5,'MAY',6,'JUN',7,'JUL',8,'AUG',9,'SEP',10,'OCT',11,'NOV',12,'DEC'),
case when (d.amount = '' OR d.amount is null) then 0 else d.amount end amount from (
select 1 month from dual
union
select 2 month from dual
UNION
select 3 month from dual
union
select 4 month from dual
UNION
select 5 month from dual
union
select 6 month from dual
UNION
select 7 month from dual
union
select 8 month from dual
UNION
select 9 month from dual
union
select 10 month from dual
UNION
select 11 month from dual
union
select 12 month from dual) t
left outer join
(Select sum(a) amount,to_char(d,'mm') month from A
where to_char(d,'YYYY')=2008
group by to_char(d,'mm')) d on
t.month = d.month ORder by t.month;
You can avoid the 't' table above using one simple table.
July 30, 2008 at 1:08 am
great man......... it is working... i modified little bit....
thanks a ton.........
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply