MYSQL the title of the book published by this publisher that has the highest num

Question

MYSQL the title of the book published by this publisher that has the highest number of onhand units collectively in all branches of henrybooks

I have these results from

SELECT SUM(OnHand) AS OnHand,BookCode,title FROM ((SELECT * FROM Book NATURAL JOIN Inventory WHERE publisherCode='JP') AS Book_Inventory) GROUP BY BookCode;

I want to select The title of the book published by this publisher that has the highest number
of onHand units collectively in all branches of Henry Books.

SELECT title FROM ((SELECT SUM(OnHand) AS OnHand,BookCode,title FROM ((SELECT * FROM Book NATURAL JOIN Inventory WHERE publisherCode='JP') AS Book_Inventory) GROUP BY BookCode)AS SUM_ONHAND) WHERE>

is what i'm trying but i get invalid use of group function

Author
authorNum
authorLast
Book
bookCode
title
publisherCode
Branch
branchNum
branchName
branchlocation
Inventory
Book
BranchNum
OnHand
Publisher
publisherCode
publisherName

Explanation / Answer

Aggreagte functions like min,max,count,avg,sum we don't use int in directly in where clause...

In the given above problem you used "WHERE Here max is the aggregate function ...

Instead if where if you keep Having clause then there is no problem. In having we use Aggregate functions

or otherwise Instead of Having you should use Subquery also....

Method1 :

SELECT title FROM ((SELECT SUM(OnHand) AS OnHand,BookCode,title FROM ((SELECT * FROM Book NATURAL JOIN Inventory WHERE publisherCode='JP') AS Book_Inventory) GROUP BY BookCode)AS SUM_ONHAND) HAVING Max(OnHand).

Method2 :

SELECT title FROM ((SELECT SUM(OnHand) AS OnHand,BookCode,title FROM ((SELECT * FROM Book NATURAL JOIN Inventory WHERE publisherCode='JP') AS Book_Inventory) GROUP BY BookCode)AS SUM_ONHAND) WHERE OnHand in (SELECT Max(OnHand) FROM Inventory)

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.