Home » SQL & PL/SQL » SQL & PL/SQL » have some problems with my sql code in oracle
have some problems with my sql code in oracle [message #431798] Thu, 19 November 2009 09:46 Go to next message
vitaly87
Messages: 2
Registered: November 2009
Junior Member
hello everyone.
i need to find costumers that the total ord in their city begger than the avg in their city.
my tables are
1. ORD (ORDID, ORDERDATE, COMMPLAN, CUSTID, SHIPDATE, TOTAL)
2. CUSTOMER(ZIP, STATE, REPID, PHONE, NAME, CUSTID, REDITLIMIT,
CITY, AREA, ADDRESS, COMMENTS)

i tried that but its wrong

SELECT CUSTOMER.NAME,ORD.CUSTID,ORD.TOTAL
FROM CUSTOMER INNER JOIN ORD
ON CUSTOMER.CUSTID=ORD.CUSTID
GROUP BY CITY
HAVING ORD.TOTAL>AVG(ORD.TOTAL)
Re: have some problems with my sql code in oracle [message #431800 is a reply to message #431798] Thu, 19 November 2009 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 63814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i tried that but its wrong

Why it's wrong?

Maybe the total ord. means the sum of all ord. for each customer.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Thu, 19 November 2009 10:24]

Report message to a moderator

Re: have some problems with my sql code in oracle [message #431815 is a reply to message #431800] Thu, 19 November 2009 11:25 Go to previous messageGo to next message
vitaly87
Messages: 2
Registered: November 2009
Junior Member
its wrote me an error:
thery is an error with :
ORA-00979 :GROUP By

Re: have some problems with my sql code in oracle [message #431818 is a reply to message #431815] Thu, 19 November 2009 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 63814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Maybe the total ord. means the sum of all ord. for each customer.


Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: have some problems with my sql code in oracle [message #431821 is a reply to message #431815] Thu, 19 November 2009 11:31 Go to previous message
BlackSwan
Messages: 24914
Registered: January 2009
Senior Member
vitaly87 wrote on Thu, 19 November 2009 09:25
its wrote me an error:
thery is an error with :
ORA-00979 :GROUP By



Fields in the GROUP BY clause must exist in the SELECT clause
Previous Topic: End of line & End of File
Next Topic: PLSQL newbie question
Goto Forum:
  


Current Time: Fri Sep 30 14:03:51 CDT 2016

Total time taken to generate the page: 0.17003 seconds