Home » SQL & PL/SQL » SQL & PL/SQL » Error in code (oracle SQL developer 10)
Error in code [message #393313] Sat, 21 March 2009 06:30 Go to next message
oraclelovers
Messages: 2
Registered: March 2009
Location: UK
Junior Member
I have two entities here:

1) BookTitle which has author, ISBN (as Primary Key), and bookTitle

2) BookCopy which has purchasePrice and copyNo

i want to give the number of book copies and the highest price paid for each book title. I want to show both the book title and author in the output.

SELECT b.bookTitle, b.author, COUNT(c.copyNo) AS "NUMBER OF COPIES BOUGHT", MAX(c.purchasePrice) "Highest Price Paid"
FROM BookCopy c, BookTitle b
WHERE c.ISBN = b.ISBN
GROUP BY b.bookTitle;
Re: Error in code [message #393315 is a reply to message #393313] Sat, 21 March 2009 06:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The error (which you did not provide in your post) should be quite obvious. You did not use all columns from the select-clause in your group by clause
Re: Error in code [message #393316 is a reply to message #393315] Sat, 21 March 2009 07:00 Go to previous messageGo to next message
oraclelovers
Messages: 2
Registered: March 2009
Location: UK
Junior Member
SELECT b.bookTitle, b.author, COUNT(c.copyNo) AS NumberOfCopiesBought, MAX(c.purchasePrice) 'HighestPricePaid'
FROM BookCopy c, BookTitle b
WHERE c.ISBN = b.ISBN
GROUP BY b.bookTitle, b.author;

Is it correct now? or I have to include attributes in involved with aggregate functions?
Re: Error in code [message #393323 is a reply to message #393313] Sat, 21 March 2009 09:47 Go to previous message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>Is it correct now?
Does it return the result set you were expecting?
Previous Topic: query
Next Topic: Tune the plsql
Goto Forum:
  


Current Time: Wed Dec 07 03:03:50 CST 2016

Total time taken to generate the page: 0.11459 seconds