To display not exit data in oracle

50 pts.
Tags:
Oracle
Oracle commands
Oracle Database
Oracle Table
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

———————————-

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following