05-24-08, 14:12 #1Registered User
- Join Date
- May 2008
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
i used the following code to get the details of the march 2007
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'));
together using one query
all help is appreciated
05-24-08, 16:54 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
>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.
05-24-08, 17:43 #3Lost Boy
Provided Answers: 4is it possible to get the sales of march 2007, and the march 2008 together using one query
- Join Date
- Jan 2004
- Croatia, Europe
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>