472,328 Members | 1,610 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,328 software developers and data experts.

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 2058
zmbd
5,501 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

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

Similar topics

2
by: SG | last post by:
Is there a stored procedure or query that will count all the records in all my tables in a database? Many thanks Sam
2
by: Shannan Casteel via AccessMonster.com | last post by:
I have an invoice form with Parts and Labor as subforms. The parts section is based on a query that pulls data from 2 tables (tblParts &...
1
by: Pascal Polleunus | last post by:
Hi, Is there an *easy* way to display a list of tables with their number of input values in psql ? Wouldn't it be useful to have the ability...
2
by: commanderjason | last post by:
This seems like a very simple question but i have never been able to find an easy answer to it. I have a user table and i do a join with...
2
by: danny_m | last post by:
Hi there I have two tables, pages and sections, of which pages are a subset of sections. I'm listing out the sections in a list and want to...
3
by: ncsthbell | last post by:
I am pulling my hair out on this! Seems like it should be easy, I just can not get it to work like I want. I know I am doing something wrong, so I...
3
by: cpanthro | last post by:
Hello, I'm using Access on XP and I am trying to count the number of cases in a Table via a query using two criteria. The criteria are being drawn...
3
by: --CELKO-- | last post by:
Is there a simple way to get a COUNT(*) for all tables in a schema in DB2 LUW 9.0?
4
by: thayes5150 | last post by:
I have an Access 2003 database linked to an MS SQL 2005 backend. We use the database as part of a process whereby we use Zone OCR to scan records...
1
by: Stevestan1 | last post by:
I am getting the "cannot produce expression ..... as part of aggregate function" error everytime I try to run a query with this expression ...
0
by: tammygombez | last post by:
Hey fellow JavaFX developers, I'm currently working on a project that involves using a ComboBox in JavaFX, and I've run into a bit of an issue....
0
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.