Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008
    Posts
    1

    Unanswered: to obtain month sales across two years

    hi all this my first post to this forum
    i have to display the monthly cost for tmarch 2007 and the march 2008
    i have table sh_sales which has the following fields

    invoice_date varchar2
    sale_amt number
    itm_code varchar2

    i used the following code to get the details of the march 2007
    HTML Code:
    SELECT   SUM(SALE_AMT),
             TO_NUMBER(TO_CHAR(INVOICE_DATE,'MM'))
    FROM     SH_SALES
    WHERE    INVOICE_DATE BETWEEN TRUNC(ADD_MONTHS(SYSDATE,- 2),'MM')
                                  AND LAST_DAY(TRUNC(ADD_MONTHS(SYSDATE,- 2),'MM'))
    GROUP BY TO_NUMBER(TO_CHAR(INVOICE_DATE,'MM'));
    
    is it possible to get the sales of march 2007, and the march 2008
    together using one query

    all help is appreciated

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >i used the following code to get the details of the march 2007
    Forgive me, but I don't think the statement above is correct.
    I think the posted code produced results for March 2008.

    >is it possible to get the sales of march 2007, and the march 2008
    >together using one query

    I am not sure I understand EXACTLY what you desire for the output.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    is it possible to get the sales of march 2007, and the march 2008 together using one query
    Yes, it is possible - you just have to tell Oracle to do that. One way to do that is to include all relevant conditions into the WHERE clause.

    Here's an example based on Scott's schema - I'll compute sum of salaries for employees who were hired in February and December 1981. You should be able to adjust this query to your problem.
    Code:
    SQL> select hiredate, sal from emp order by hiredate;
    
    HIREDATE        SAL
    -------- ----------
    17.12.80        800
    20.02.81       1600    <-
    22.02.81       1250    <-
    02.04.81       2975
    01.05.81       2850
    09.06.81       2450
    08.09.81       1500
    28.09.81       1250
    17.11.81       5000
    03.12.81        950   <-
    03.12.81       3000   <-
    23.01.82       1300
    09.12.82       3000
    12.01.83       1100
    
    14 rows selected.
    
    SQL> select to_char(hiredate, 'mm.yyyy') my, sum(sal) sum_sal
      2  from emp
      3  where to_char(hiredate, 'mm.yyyy') in ('02.1981', '12.1981')
      4  group by to_char(hiredate, 'mm.yyyy');
    
    MY         SUM_SAL
    ------- ----------
    02.1981       2850
    12.1981       3950
    
    SQL>

Posting Permissions

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