CODEWITHSUNDEEP
sql
Vicktor
Vicktor

Reputation: 13

Aggregate Functions And GROUP BY

I have following query as homework for sql:-

Write a query that tells how many books each author has written. On each row provide first the authorid, followed by the number of books. Order the listing so that the author with the most books written is at the top, and at the bottom are those that have written the fewest number of books. The authors that the same number of books written are further ordered by their AuthorID's in an increasing order.

I have already tried different options via replacing groupby, orderby and count() functions.

SELECT authorid as "authorid",
        COUNT (bookid) as "count"
FROM book
GROUP by authorid
HAVING COUNT (bookid) >=1
ORDER BY   COUNT (bookid) DESC;

Your result

authorid    count
204 4
202 3
206 1
201 1
207 1
205 1

Expected result

authorid    count
204 4
202 3
201 1
205 1
206 1
207 1

Upvotes: 1

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

You are missing this condition:

The authors that the same number of books written are further ordered by their AuthorID's in an increasing order.

That requires a second ORDER BY key:

ORDER BY COUNT(bookid) DESC, authorid;

Upvotes: 5

Related Questions