Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: Query Output in a formatted way required

    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 2001
    Location
    UK
    Posts
    4,650

    Re: Query Output in a formatted way required

    An example .... SALES_DATA will be your permanent table ...
    HTH

    Sathyaram

    with
    sales_data(month,rvnu) as
    (
    values
    ('JAN',1000),
    ('MAR',4000)
    ),


    months(mname) as
    (
    values
    ('JAN'),
    ('FEB'),
    ('MAR')
    )

    select
    mname,
    case when rvnu is null then 0 else rvnu end
    from
    months
    left outer join
    sales_data
    on mname=month



    Originally posted by srini_gs
    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?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2004
    Posts
    4

    Re: Query Output in a formatted way required

    Hi,

    thanks.. but you have to create another table for months I suppose



    QUOTE]Originally posted by sathyaram_s
    An example .... SALES_DATA will be your permanent table ...
    HTH

    Sathyaram

    with
    sales_data(month,rvnu) as
    (
    values
    ('JAN',1000),
    ('MAR',4000)
    ),


    months(mname) as
    (
    values
    ('JAN'),
    ('FEB'),
    ('MAR')
    )

    select
    mname,
    case when rvnu is null then 0 else rvnu end
    from
    months
    left outer join
    sales_data
    on mname=month
    [/QUOTE]

Posting Permissions

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