We help IT Professionals succeed at work.

Count for Day of Current Month Show Zero Values

253 Views
Last Modified: 2012-05-10
Please refer to a previous question I posted about counts per month.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_26459915.html

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)
GROUP BY m
ORDER BY m ASC;
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.