Aggregate function, count or max, on >2 joined tables where pk is composite

[mmclancy]

How do I use an aggregate function, e.g. count or max of dvds where I have 4 tables & the table I want to get a count or max from (rental) has a composite key that is a combo of dvd and copy

How do I use an aggregate function, e.g. count or max of dvds where I have 4 tables & the table I want to get a count or max from (rental) has a composite key that is a combo of dvd and copy. I want to know the number of dvds sold out/not sold out (compare the number of dvds out but not returned/returned in rental table to the numbercopies in dvd)
Tables
dvd: dvd_id, title, numbercopies
copy: copy_id
dvdcopy: dvdcopy_id, dvd_id, copy_id
rental: rental_id, dvdcopy_id, rentaldate , rentalreturn

I've tried using Oracle's SQL Developer to do the following:
(for dvds sold out)

SELECT dc.dvd_id, 
   dc.title, 
   dc.copyid, 
   COUNT(d.dvd_id) 
       AS numberdvd, 
FROM dvdcopy dc
   JOIN dvd d 
      ON dc.dvd_id=m.dvd_id
   JOIN rental r 
      ON r.dvdcopy_id=d.dvd_id
WHERE rentaldate Is Not Null 
   AND rentalreturn Is Null;
GROUP BY dc.dvd_id, 
   dc.copy_id;

My code gives me a ORA-00937'Not a single-group group function' error message. I would appreciate any help!

[zmbd]

Did you mean to place the semicolon, ";", at the end of the WHERE clause (Line12)? I did not remove it while formatting your posted SQL just to be sure it was intended vs. typo.

-
This is a fairly heavy read: Oracle - Aggregate Functions it also has a link to the GROUP BY clause and that is a REALLY heavy read.

[rski]

First of all you have to add dc.title column to group by clause.

Next, what is m in

ON dc.dvd_id=m.dvd_id
 

?

If alias m is for dvd (should be d) then also change

COUNT(d.dvd_id)
 

to

COUNT(dc.dvd_id)
 

What about this query

SELECT dc.dvd_id, 
   dc.title, 
   dc.copyid, 
   COUNT(dc.dvd_id) 
       AS numberdvd, 
FROM dvdcopy dc
   JOIN dvd d 
      ON dc.dvd_id=d.dvd_id
   JOIN rental r 
      ON r.dvdcopy_id=d.dvd_id
WHERE rentaldate Is Not Null 
   AND rentalreturn Is Null;
GROUP BY dc.dvd_id, 
   dc.copy_id,dc.title;