Home » SQL & PL/SQL » SQL & PL/SQL » Problem with (seemingly) simple sql query (oracle 10g)
Problem with (seemingly) simple sql query [message #447307] Sat, 13 March 2010 08:25 Go to next message
will1990
Messages: 5
Registered: March 2010
Location: UK
Junior Member
Hello! This is my first post!

I am having trouble with a is seemingly meant to be a very simple joining query with a count. However, I keep getting the following error:


ERROR at line 1:
ORA-00979: not a GROUP BY expression





With this query:


 select memberno, name, count(*) as "Total Matches Won"
 FROM members, matches                                                        
 where members.memberno = matches.winmember
 group by members.memberno
 order by memberno;



I may as well tell everyone what i have tried and what i think the error may be....

I have tried to remove the prefixes on each of the table and column names, this did not help.

I also removed the count, order by and group by and the query executed successfully.

I feel the problem may be connected to using two different table with different column names. (although the winmember is the foreign key of memberno).

I am used to using mysql (despite these being v. similar) i still feel that this problem is very easy to fix and I am being a bit dense... so sorry about that!

Finally, any advice on how to sort this problem would be AMAZING, and I would be very grateful!
Re: Problem with (seemingly) simple sql query [message #447315 is a reply to message #447307] Sat, 13 March 2010 08:38 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
please post DDL fro your tables so we can run your SQL, too.
Re: Problem with (seemingly) simple sql query [message #447316 is a reply to message #447307] Sat, 13 March 2010 08:42 Go to previous messageGo to next message
will1990
Messages: 5
Registered: March 2010
Location: UK
Junior Member
This inserts all the data into the tables. I will post another create table ddl.


thanks

Will
Re: Problem with (seemingly) simple sql query [message #447317 is a reply to message #447316] Sat, 13 March 2010 08:45 Go to previous messageGo to next message
will1990
Messages: 5
Registered: March 2010
Location: UK
Junior Member
Here is the table creation ddl as promised.

As i mentioned earlier, since this i have updated the members.memberno as a primary key and matches.winmember as a forgein key that references members.memberno

here are the two queries i ran to perform this.


alter table members
add PRIMARY KEY(MEMBERNO);


alter table matches
add FOREIGN KEY(WINMEMBER) REFERENCES members(memberno);




Thanks need anything else just say.
  • Attachment: makenames.sql
    (Size: 111.14KB, Downloaded 620 times)
Re: Problem with (seemingly) simple sql query [message #447319 is a reply to message #447307] Sat, 13 March 2010 08:56 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I would say that the error is quite self-explanative: Quote:
not a GROUP BY expression

I do not know other RDBMSs, but in Oracle, you cannot use an expression in SELECT list, which is not present in a GROUP BY clause without using aggregate function. The query SELECTs MEMBERNO and NAME. It is GROUPed BY MEMBERNO. I wonder, what the query should return, if the table(s) contain multiple rows with different NAMEs for the same MEMBERNO.

Oracle throws error in this case. According to your need, either add NAME into GROUP BY clause or use aggregate function (SUM, MIN, MAX) on NAME. It depends on query requirements, only you know.
Re: Problem with (seemingly) simple sql query [message #447320 is a reply to message #447307] Sat, 13 March 2010 09:03 Go to previous messageGo to next message
will1990
Messages: 5
Registered: March 2010
Location: UK
Junior Member
You were 100% correct, I just edit the code according to your suggestions and it work fine.

Thanks alot for your help, do you ahve any suggested reading that would help me grasp this fully?



Many thanks

Will
Re: Problem with (seemingly) simple sql query [message #447321 is a reply to message #447320] Sat, 13 March 2010 09:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database SQL Reference

http://www.orafaq.com/forum/m/382787/102589/?#msg_382787

Regards
Michel
Re: Problem with (seemingly) simple sql query [message #447324 is a reply to message #447307] Sat, 13 March 2010 09:24 Go to previous messageGo to next message
will1990
Messages: 5
Registered: March 2010
Location: UK
Junior Member
I'm trying a number of queries as i am aiming to learn the syntax,

I have expanded the current query to use a sub query and now hit an error that im reading up on...

New query:



select memberno,name, count(matches.winmember) as "Total Wins"
FROM members, matches       
where members.memberno = matches.winmember
[color=red]and count(matches.winmember) > ( select memberno,name, count(matches.losemember)
FROM members, matches[/color]
where members.memberno = matches.loosemember)
group by members.memberno, members.name
order by members.name;
 


Iget the erorr

ORA-00934: group function is not allowed here


seems pretty self expanitory so i removed the group by clause which did not help. I think am missing somthing in the way i am thinking here....

Any ideas?

PS. Thanks for the reading, should keep me busy tonight!!!

Will
Re: Problem with (seemingly) simple sql query [message #447325 is a reply to message #447324] Sat, 13 March 2010 09:38 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
SELECT can contain HAVING clause which allows filter condition(s) on aggregates. It is described (with syntax and examples) in SQL Reference book, available e.g. online on http://tahiti.oracle.com/ (or directly for 10gR2 in the first link Michel posted).
Previous Topic: Interval Partitioning and NULL Column Values
Next Topic: Trim Column Data
Goto Forum:
  


Current Time: Mon Dec 05 18:56:12 CST 2016

Total time taken to generate the page: 0.22749 seconds