Thanks for the question, Siva.
Asked: August 31, 2016 - 3:53 am UTC
Last updated: August 31, 2016 - 7:51 am UTC
Version: Oracle 11g
Viewed 10K+ times! This question is 
You Asked
Hello,
After executing the Query Month Starts from APR to SEP only data is available in database and displaying properly.
If there is no data i would like to display Month and Count as 0 with the same result.
Ex: Here January(01) month has no record in database It should display like below,similar to December(12) month too.
YEAR MONTH TOTAL_PAGE_VISITOR
2015 01 0
2015 02 8
2015 03 176
2015 04 369
2015 05 130
2015 06 148
2015 07 296
2015 08 44
2015 09 44
2015 11 38
2015 12 0
SELECT to_char(Cns.Requestdate,'YYYY') As Year,to_char(Cns.Requestdate,'MM') As Month,SUM(Cns.Pagevisitors) AS TOTAl_PAGE_VISITOR FROM Shortid Sh
INNER JOIN Countername Cn ON Sh.Id = Cn.Shortid_id
INNER JOIN Countername_Summary Cns ON Cn.Id = Cns.Countername_Id
INNER JOIN Countrycode Cd ON Cd.Id = Cn.Countrycode_Id
WHERE Cns.Pagevisitors IS NOT NULL AND to_char(Cns.Requestdate,'YYYY') ='2015'
AND Sh.Shortid like '%wcms_bnn_-news_detail_page-%'
GROUP BY to_char(Cns.Requestdate, 'YYYY'),TO_CHAR(Cns.Requestdate, 'MM')
ORDER BY TO_CHAR(Cns.Requestdate, 'MM') ASC;
How to modify the query to get the 0 in the count with month and year?
Thank you.
How to get the
and Connor said...
First we'll create a table that is "missing" some months
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t as
2 select rownum r, add_months(date '2016-01-01',rownum-1) dte, ceil(dbms_random.value(0,100)) amt
3 from dual
4 connect by level <= 12;
Table created.
SQL>
SQL> select * from t;
R DTE AMT
---------- --------- ----------
1 01-JAN-16 25
2 01-FEB-16 69
3 01-MAR-16 9
4 01-APR-16 45
5 01-MAY-16 47
6 01-JUN-16 78
7 01-JUL-16 30
8 01-AUG-16 64
9 01-SEP-16 19
10 01-OCT-16 36
11 01-NOV-16 93
12 01-DEC-16 17
12 rows selected.
SQL>
SQL> delete from t
2 where r in ( 1,4,6 );
3 rows deleted.
SQL>
SQL> select * from t;
R DTE AMT
---------- --------- ----------
2 01-FEB-16 69
3 01-MAR-16 9
5 01-MAY-16 47
7 01-JUL-16 30
8 01-AUG-16 64
9 01-SEP-16 19
10 01-OCT-16 36
11 01-NOV-16 93
12 01-DEC-16 17
9 rows selected.
To fill in the blanks, we need something that has the full 12 months so we can outer join from it to "T". But we just saw above how to generate 12 months of rows, so we can use the same concept:
SQL>
SQL>
SQL> with all_the_months as
2 (
3 select add_months(date '2016-01-01',rownum-1) mth
4 from dual
5 connect by level <= 12
6 )
7 select a.mth, nvl(t.amt,0)
8 from t,
9 all_the_months a
10 where a.mth = t.dte(+)
11 order by 1;
MTH NVL(T.AMT,0)
--------- ------------
01-JAN-16 0
01-FEB-16 69
01-MAR-16 9
01-APR-16 0
01-MAY-16 47
01-JUN-16 0
01-JUL-16 30
01-AUG-16 64
01-SEP-16 19
01-OCT-16 36
01-NOV-16 93
01-DEC-16 17
12 rows selected.
SQL>
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment