Count for Day of Current Month Show Zero Values

Posted on 2010-09-15
Last Modified: 2012-05-10
Please refer to a previous question I posted about counts per month.

I would like to do the same thing but count for each day of current month, and if there is a zero count, i would like that to show as well.

Here was the query the was worked out for count of month.

SELECT TO_CHAR(m, 'mm yyyy') AS themonth, COUNT(q.quote_id) AS numoflosses
    FROM     q_quotes q
         RIGHT JOIN
             (    SELECT TO_DATE('2009' || TO_CHAR(LEVEL, 'fm09'), 'yyyymm') m
                    FROM DUAL
              CONNECT BY LEVEL <= 12)
         ON q.quote_status_id IN (8, 10, 13)
        AND q.quote_dt >= m
        AND q.quote_dt < ADD_MONTHS(m, 1)
        AND q.quote_id NOT IN (  SELECT linked_quote_id FROM q_quote_links)
Question by:theideabulb
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 500 total points
ID: 33683029
Off the top of my head change the connect by to something like:

SELECT TO_DATE('2009' || to_char(sysdate,'mm') || TO_CHAR(LEVEL, 'fm09'), 'yyyymmdd') m
CONNECT BY LEVEL <= to_number(to_char(last_day(sysdate),'dd'))

Expert Comment

ID: 33692140
I've always resorted to getting the recordset and then looping over it and putting the results into a new handmade recordset and adding the missing ones myself.  I could never find a way to make a DB return a result where no results exist.

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question