469,126 Members | 1,256 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,126 developers. It's quick & easy.

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

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)
Expand|Select|Wrap|Line Numbers
  1. SELECT dc.dvd_id, 
  2.    dc.title, 
  3.    dc.copyid, 
  4.    COUNT(d.dvd_id) 
  5.        AS numberdvd, 
  6. FROM dvdcopy dc
  7.    JOIN dvd d 
  8.       ON dc.dvd_id=m.dvd_id
  9.    JOIN rental r 
  10.       ON r.dvdcopy_id=d.dvd_id
  11. WHERE rentaldate Is Not Null 
  12.    AND rentalreturn Is Null;
  13. GROUP BY dc.dvd_id, 
  14.    dc.copy_id;
My code gives me a ORA-00937'Not a single-group group function' error message. I would appreciate any help!
Dec 4 '12 #1
2 1980
zmbd
5,400 Expert Mod 4TB
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.
Dec 4 '12 #2
rski
700 Expert 512MB
First of all you have to add dc.title column to group by clause.

Next, what is m in
Expand|Select|Wrap|Line Numbers
  1. ON dc.dvd_id=m.dvd_id
  2.  
?

If alias m is for dvd (should be d) then also change
Expand|Select|Wrap|Line Numbers
  1. COUNT(d.dvd_id)
  2.  
to
Expand|Select|Wrap|Line Numbers
  1. COUNT(dc.dvd_id)
  2.  


What about this query
Expand|Select|Wrap|Line Numbers
  1. SELECT dc.dvd_id, 
  2.    dc.title, 
  3.    dc.copyid, 
  4.    COUNT(dc.dvd_id) 
  5.        AS numberdvd, 
  6. FROM dvdcopy dc
  7.    JOIN dvd d 
  8.       ON dc.dvd_id=d.dvd_id
  9.    JOIN rental r 
  10.       ON r.dvdcopy_id=d.dvd_id
  11. WHERE rentaldate Is Not Null 
  12.    AND rentalreturn Is Null;
  13. GROUP BY dc.dvd_id, 
  14.    dc.copy_id,dc.title;
  15.  
Dec 5 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by SG | last post: by
2 posts views Thread by Shannan Casteel via AccessMonster.com | last post: by
1 post views Thread by Pascal Polleunus | last post: by
2 posts views Thread by commanderjason | last post: by
2 posts views Thread by danny_m | last post: by
3 posts views Thread by --CELKO-- | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.