Home » SQL & PL/SQL » SQL & PL/SQL » QUERY (Oracle 10g)
QUERY [message #383394] Wed, 28 January 2009 12:42 Go to next message
pantherzzzz
Messages: 1
Registered: January 2009
Junior Member
Hello coders!
I'm trying to write an SQL command for revision purposes...to display for each publisher with more than one author, the publisher’s name, the publisher’s location and the average cost of the books that the publisher sells. I'm unable to "put it all together" which I must add is not through a lack of effort. I've attached the tables(SECTION A Q4) and below is my "no good" code. I seem to be going round and round.

/*ATTEMPT to Display the publishers name with nore than 1 author, which returns an error that the group function is not allowed here ofcourse.*/
select p.name,count(distinct b.authorName) from Publisher p,Book b  
where count(b.authorName)>1; 

// Attemp to get the publishers with more than 1 title
select count(distinct a.publisherName) from Book a,Book b
where b.authorName=a.authorName 
having count(a.publisherName)>1;


Please excuse my wording if I've not explained it well and look forward to hearing from you....

Thanks


[Edit MC: formatting]

[Updated on: Thu, 29 January 2009 00:44] by Moderator

Report message to a moderator

Re: QUERY [message #383406 is a reply to message #383394] Wed, 28 January 2009 15:21 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Re: QUERY [message #383505 is a reply to message #383394] Thu, 29 January 2009 00:46 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Study the examples of use of "GROUP BY" and "HAVING" together in SQL Reference.

Regards
Michel
Previous Topic: Roll Forward
Next Topic: how to get max semester and year (merged 3)
Goto Forum:
  


Current Time: Thu Dec 08 12:40:01 CST 2016

Total time taken to generate the page: 0.20574 seconds