To display not exit data in oracle
in my table there a,b,c,d are four columns.i have to sum(b) and sum(c). for a perticular date.but the query should show 0 for clumn b & c where there no value has been entered.suppose b & c are summing per month.but in some month there is no entry for a,b,c,d.so how to show them for that month as 0 for all columns.?
plz suggest how i will write.
Software/Hardware used:
oracle
Software/Hardware used:
oracle
ASKED:
August 10, 2010 4:07 PM
UPDATED:
August 10, 2010 5:01 PM
Answer Wiki:
You will probably need to create a table with the months of the year, and outer-join it to your main table.
Or, you could use an in-line view or subquery factoring (depending on the Oracle version) to achieve the same result.
Here's an example:
<pre>WITH months AS
(SELECT 1 mm,'JANUARY' month FROM dual
UNION SELECT 2,'FEBRUARY' FROM dual
UNION SELECT 3,'MARCH' FROM dual
UNION SELECT 4,'APRIL' FROM dual
UNION SELECT 5,'MAY' FROM dual
UNION SELECT 6,'JUNE' FROM dual
UNION SELECT 7,'JULY' FROM dual
UNION SELECT 8,'AUGUST' FROM dual
UNION SELECT 9,'SEPTEMBER' FROM dual
UNION SELECT 10,'OCTOBER' FROM dual
UNION SELECT 11,'NOVEMBER' FROM dual
UNION SELECT 12,'DECEMBER' FROM dual)
<b>SELECT m.month,SUM(NVL(C,0)),SUM(NVL(D,0))
FROM your_table t
RIGHT OUTER JOIN months m
ON TO_CHAR(t.date_x,'mm') = m.mm
GROUP BY m.mm,m.month
ORDER BY m.mm;</b></pre>
----------------------------------
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
