Home » SQL & PL/SQL » SQL & PL/SQL » Monthly Totals (Oracle 9i)
Monthly Totals [message #294344] Thu, 17 January 2008 05:05 Go to next message
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 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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

Previous Topic: getting error when executing procedure(ORA-01562:)
Next Topic: Using a non-literal - Illegal parameter in SYS_CONNECT_BY_PATH
Goto Forum:
  


Current Time: Fri Dec 09 15:54:04 CST 2016

Total time taken to generate the page: 0.13247 seconds