SQLTeam.com | Weblogs | Forums

SOLVED ! ORA-00979: not a GROUP BY expression when trying to group joint tables


#1

Hi guys, I've been trying this for hours but keep getting the same error. I'm trying to join the tables, but eliminate repeated data, hence the group by function.

The SQL is as follows:

select d.directorno, name
from film f, director d
where f.directorno=d.directorno and genre !='SCIENCE FICTION'
group by d.directorno

Without the group by function, it gives me repeated entries, does anyone have any idea on how to fix this?


#2

You need an aggregate operator on the "name" column. Or group by both directorno and name


#3

Thanks ! I have one other question, right now I have:
Customer: custno(PK), name, address, DVDs, registration
Rental: dvdno(FK), custno(FK), hiredate, returndate
DVD: dvdno(PK), filmno, dateofpurchase
Film: filmno(PK), title, year, genre, director(FK), star(FK)
Star: Starno(PK), name, birthplace, birthyear, deathyear
Director: directorno(PK), name, birthyear, deathyear

However, I have been tasked with having more than one star per film. Right now my database only allows one star per film. Do you have any idea how I would amend this? Would it require an extra table?


#4

Looks like it. You could have a FilmStarLink table with columns [filmno], [Starno] and appropriate FKeys (and move data from [star] to the FilmStarLink table, and then drop the [star] column in [Film] table.


#5

@Kristen Cheers ! :slight_smile: