Home » SQL & PL/SQL » SQL & PL/SQL » Group by
Group by [message #237985] Wed, 16 May 2007 04:57 Go to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

May anyone check for me this code, what did I miss?

select O.Spnum, Sname, to_char(Odate,'mm/yyyy') Omonth, count(Onum) OrderNum, sum(Orderqty) TotalUnits, sum(Amtdue) TotalAmount from SupplierDetails S, OrderDetail O where S.Spnum=O.Spnum group by O.Spnum, Sname, to_char(trunc(Odate),'mm/yyyy') Omonth, count(Onum) OrderNum, sum(Orderqty) TotalUnits, sum(Amtdue) TotalAmount;
commit;


It displays the error:

ERROR at line 1:
ORA-00933: SQL command not properly ended


Thanks.

Sincerely,

Vickie

[Updated on: Wed, 16 May 2007 04:58]

Report message to a moderator

Re: Group by [message #237988 is a reply to message #237985] Wed, 16 May 2007 05:13 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
where is the from table-name
Ashu
Re: Group by [message #237993 is a reply to message #237988] Wed, 16 May 2007 05:28 Go to previous messageGo to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

It from SupplierDetails and OrderDetail tables.


select O.Spnum, Sname, to_char(Odate,'mm/yyyy') Omonth, count(Onum) OrderNum, sum(Orderqty) TotalUnits, sum(Amtdue) TotalAmount from SupplierDetails S, OrderDetail O where S.Spnum=O.Spnum group by O.Spnum, Sname, to_char(trunc(Odate),'mm/yyyy') Omonth, count(Onum) OrderNum, sum(Orderqty) TotalUnits, sum(Amtdue) TotalAmount;
commit;

[Updated on: Wed, 16 May 2007 05:28]

Report message to a moderator

Re: Group by [message #238003 is a reply to message #237993] Wed, 16 May 2007 05:52 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
why making Omonth alias there?

Quote:
select O.Spnum, Sname, to_char(Odate,'mm/yyyy') Omonth, count(Onum) OrderNum, sum(Orderqty) TotalUnits, sum(Amtdue) TotalAmount
from SupplierDetails S, OrderDetail O
where S.Spnum=O.Spnum
group by O.Spnum, Sname, to_char(trunc(Odate),'mm/yyyy'), count(Onum) OrderNum, sum(Orderqty) TotalUnits, sum(Amtdue) TotalAmount;
Re: Group by [message #238009 is a reply to message #238003] Wed, 16 May 2007 06:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You should not use aliases in a group by, plus, you cannot group by those group-columns (sum, count, whatever)
Re: Group by [message #238126 is a reply to message #238009] Wed, 16 May 2007 15:14 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
It usually helps troubleshoot if you spread it out a bit.

First, I'm assuming you meant "S.name" rather than Sname in the select section. Your WHERE clause isn't going to work.


Second, you don't need a commit unless you're doing an update.

Third, I don't think you need to have the aliases in the "group by" section, as previously mentioned.

HTH,
Ron

select 
	O.Spnum, 
	Sname, 
	to_char(Odate,'mm/yyyy') Omonth,  
	count(Onum) OrderNum,  
	sum(Orderqty) TotalUnits,  
	sum(Amtdue) TotalAmount  
from  
	SupplierDetails S,  
	OrderDetail O 
where  
	S.Spnum=O.Spnum 
group by  
	O.Spnum,  
	Sname,  
	to_char(trunc(Odate),'mm/yyyy') Omonth,  
	count(Onum) OrderNum,  
	sum(Orderqty) TotalUnits,  
	sum(Amtdue) TotalAmount;
commit;
Re: Group by [message #238127 is a reply to message #238126] Wed, 16 May 2007 15:18 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member

I didn't notice until I re-read my posted reply, but you need to need to fix all your column names...

select 
	O.Spnum, 
	S.name, 
	to_char(O.date,'mm/yyyy') Omonth,  
	count(O.num) OrderNum,  
	sum(O.orderqty) TotalUnits,  
	sum(Amtdue) TotalAmount    --<---- which table does this come from, O or S?
from  
	SupplierDetails S,  
	OrderDetail O 
where  
	S.Spnum=O.Spnum 
group by  
	O.Spnum,  
	S.name,  
	to_char(trunc(O.date),'mm/yyyy') Omonth,  
	count(O.num) OrderNum,  
	sum(O.orderqty) TotalUnits,  
	sum(Amtdue) TotalAmount;
commit;

Re: Group by [message #238139 is a reply to message #238127] Wed, 16 May 2007 17:53 Go to previous messageGo to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

Sname is a column name means Supplier Name
Odate is a column name means Open Date ( Order Date)
OrderNum,TotalUnits,TotalAmount are not the 'real' column. These columns are not already exist in any tables.


The query is: displays number of Orders, Total Units and Total Amount according to each supplier and the month/year of the order open date.

I fixed the code as what your guys said. But the erro still.

SQL> column Spnum heading 'Supplier|Number' format 99
SQL> column Sname heading 'Supplier|Name' format a20
SQL> column Omonth heading 'Order|Month' format a10
SQL> column OrderNum heading 'Number of|Orders' forma
SQL> column TotalUnits heading 'Total|Units' format 9
SQL> column TotalAmount heading 'Total|Amount' format
SQL> 
SQL> select
  2          O.Spnum,
  3          S.Sname,
  4          to_char(Odate,'mm/yyyy') Omonth,
  5          count(Onum) OrderNum,
  6          sum(Orderqty) TotalUnits,
  7          sum(Amtdue) TotalAmount
  8  from
  9          SupplierDetails S,
 10          OrderDetail O
 11  where
 12          S.Spnum=O.Spnum
 13  group by
 14          S.Spnum,
 15          S.Sname,
 16          to_char(Odate,'mm/yyyy') Omonth,
 17          count(Onum) OrderNum,
 18          sum(Orderqty) TotalUnits,
 19          sum(Amtdue) TotalAmount;
        to_char(Odate,'mm/yyyy') Omonth,
                                 *
ERROR at line 16:
ORA-00933: SQL command not properly ended

[Updated on: Wed, 16 May 2007 17:54]

Report message to a moderator

Re: Group by [message #238140 is a reply to message #237985] Wed, 16 May 2007 18:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
SQL statement can NOT end with a comma
Re: Group by [message #238141 is a reply to message #238140] Wed, 16 May 2007 18:07 Go to previous messageGo to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

it does not end with a comma. It ends with ;
Re: Group by [message #238142 is a reply to message #237985] Wed, 16 May 2007 18:24 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
My Bad.
That is what I get for speed reading.
group by
 O.Spnum,
 S.Sname,
 to_char(Odate,'mm/yyyy');
Re: Group by [message #238144 is a reply to message #238142] Wed, 16 May 2007 18:33 Go to previous messageGo to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

thank you so much.

Sincerely,

Van
Re: Group by [message #238352 is a reply to message #238139] Thu, 17 May 2007 09:09 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
You should still qualify ALL column names in your entire SQL statement with the table designation you've created for each table.


You wrote...
SQL> select
  2          O.Spnum,
  3          S.Sname,
  4          to_char(Odate,'mm/yyyy') Omonth,
  5          count(Onum) OrderNum,
  6          sum(Orderqty) TotalUnits,
  7          sum(Amtdue) TotalAmount
  8  from
  9          SupplierDetails S,
 10          OrderDetail O
 11  where
 12          S.Spnum=O.Spnum
 13  group by
 14          S.Spnum,
 15          S.Sname,
 16          to_char(Odate,'mm/yyyy') Omonth,
 17          count(Onum) OrderNum,
 18          sum(Orderqty) TotalUnits,
 19          sum(Amtdue) TotalAmount;
        to_char(Odate,'mm/yyyy') Omonth,


While this may work (with replacing the final "," with ";"), Odate, Onum, Orderqty and Amtdue should all be written with "S." or "O.", depending on which table they're coming from. If you get in the habit of specifically identifying each column (O.Orderqty rather than Orderqty), you'll save yourself problems down the line.

BTW, It looks like you moved the line "to_char(Odate,'mm/yyyy') Omonth," from above TotalAmount to below it, which is why you have a comma at the end of Omonth and a semi-colon at the end of TotalAmount.

HTH,
Ron
Re: Group by [message #238361 is a reply to message #238352] Thu, 17 May 2007 10:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The error is not in a comma. If you look closely, the indicated line ending in a comma does not have a line number; it is sqlplus echoing the offending line.
As stated several posts ago the errors are
- aliases are not allowed in group by
- aggregate functions (sum, count, etc) are not allowed in group by
SQL> drop table faq;

Table dropped.

SQL> create table faq(id number, my_number number);

Table created.

SQL> insert all
  2  into faq values (1, 2)
  3  into faq values (1, 3)
  4  into faq values (2, 6)
  5  into faq values (3, 3)
  6  select * from dual;

4 rows created.

SQL> Rem Correct
SQL> select id
  2  ,	    sum(my_number) a
  3  from   faq
  4  group  by id
  5  ;

        ID          A
---------- ----------
         1          5
         2          6
         3          3

SQL> Rem Alias
SQL> select id
  2  ,	    sum(my_number) a
  3  from   faq
  4  group  by id my_id
  5  ;
group  by id my_id
             *
ERROR at line 4:
ORA-00933: SQL command not properly ended


SQL> Rem using sum
SQL> select id
  2  ,	    sum(my_number) a
  3  from   faq
  4  group  by id
  5  ,	    sum(my_number)
  6  ;
,      sum(my_number)
       *
ERROR at line 5:
ORA-00934: group function is not allowed here
Re: Group by [message #238416 is a reply to message #238361] Thu, 17 May 2007 22:43 Go to previous message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

yup. the error is not a comma as Frank indicated.

I took out the aliases and the aggregate functions. the problem solved. Smile

Sincerely,

vickie
Previous Topic: how we call shellscripts from PL/SQL programs
Next Topic: how to search a value in all tables
Goto Forum:
  


Current Time: Sat Dec 03 08:24:57 CST 2016

Total time taken to generate the page: 0.15838 seconds