Results 1 to 6 of 6

Thread: Query Output

  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Question Unanswered: Query Output

    I have a sales table. I have to generate monthly report on the sales done. The query is like this

    Select to_char(sales_data,'MMM'),count(1) from sales group by to_char(sales_data,'MMM')

    The output is like this

    JAN 20
    FEB 30
    APR 30
    MAY 40

    If you see, it does not show any value for month of march as no sales was done in that month. I want the query to rechanged so that it shows the month of march and sales as 0 against it like this:


    JAN 20
    FEB 30
    MAR 0
    APR 30
    MAY 40

    How can I rewrite the query?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    use NVL

  3. #3
    Join Date
    Mar 2004
    Posts
    4
    Originally posted by anacedent
    use NVL
    Can you pl. rewrite the query?

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    i don't think NVL will work since you have zero entries for
    March.

    you first need a list of all months and then go get counts for each month.

    how you ask?
    you could create a table with all valid months in it if you wanted.

    or this might work.
    This is cut from a piece of Tom Kite's code.
    It will give you a list of all months in the year:
    PHP Code:
    SELECT DISTINCT TO_CHAR(dt,'MM Mon YYYY'the_month
    FROM 
    (
    SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt
     FROM all_objects
    WHERE ROWNUM 
    <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'); 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Mar 2004
    Posts
    14
    The Duck right - you need a month list , but if you need only current years months may be it's easy do just like this

    ( SELECT rownum month FROM all_objects WHERE ROWNUM <= 12 )

    or

    ( SELECT rownum month FROM all_objects WHERE ROWNUM <= to_char(SYSDATE,’MM’))

    if you need the restriction on current month

    so query will like that

    select month, nvl(cnt_,0) from

    ( select month ,count(*) cnt_ from sales group by month ) sales ,
    ( SELECT rownum month FROM all_objects WHERE ROWNUM <= 12 ) months

    where months.month = sales.month (+)

    and you'll need to change months query add years if you need not one year period

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Andrev has it.
    He will lead you to the promised land!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •