Monthly Totals [message #294344] |
Thu, 17 January 2008 05:05  |
kthelai
Messages: 1 Registered: January 2008 Location: Accra
|
Junior Member |
|
|
Hi,
I am very new to oracle and need your help on this.
The script i have below is for an annual report on imports
clear column
spool C:\endofyear07.txt
set underline off
set linesize 70
set pagesize 30
ttitle center 'FISH IMPORT SUMMARY FROM JAN-DEC 2007'skip -
right'Page: 'format 9 sql.pno skip 2
column permit_id format a15 heading 'PERMITS|----------'
column qty_ordered format 9999999.99 heading 'QUANTITY|ORDERED|--------'
column fob format 99999999999999 heading 'FREE_ON|BOARD|-----------'
column name format a30 heading 'VESSEL|NAME|-------------'
column pordate format date heading 'DATE_IN |PORT|---------'
column firstname format a15 heading 'FIRSTNAME|----------'
column lastname format a15 heading 'LASTNAME|-------------'
select firstname,lastname ,permit_id ,Qty_ordered,fob,name,portdate
from company natural join permits natural join orders natural join order_fob natural join vessels
where portdate between '01-JAN-07'and'31-DEC-07'
order by portdate;
clear column
spool off
NOW the following statements:
select count(orderid),sum(Qty_ordered)
from company natural join permits natural join orders natural join order_fob natural join vessels
where portdate between '01-JAN-07'and'31-DEC-07'
/
select count(orderid),sum(fob)
from company natural join permits natural join orders natural join order_fob natural join vessels
where portdate between '01-JAN-07'and'31-DEC-07'
/
give me the sums for Qty_ordered and fob for the year.
MY PROBLEM:
is that i want to find the sum(Qty_ordered) and sum(fob) for each month as well.
I tried
GROUP BY on portdate
but got an error saying
ORA-00979: not a GROUP BY EXPRESSION
I'm still trying but i'd really appreciate some help.
Thanks
|
|
|
Re: Monthly Totals [message #294351 is a reply to message #294344] |
Thu, 17 January 2008 05:23  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
First, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).
Next, "between '01-JAN-07'and'31-DEC-07'" is wrong:
SQL> select * from dual where sysdate between '01-JAN-07'and'31-DEC-07';
select * from dual where sysdate between '01-JAN-07'and'31-DEC-07'
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
This will give you a clue:
SQL> select sysdate, extract(month from sysdate) month from dual;
SYSDATE MONTH
------------------- ----------
17/01/2008 12:22:56 1
1 row selected.
Regards
Michel
|
|
|