Substraction Between MAX and Count

Question

I want to get the difference between the max quantity of books written by an author and the quantity writed by each author.

SELECT  count(book_name) contar, (MAX(C.qty) - count(book_name)) final
FROM (select B.author_id, count(book_name) qty 
     FROM author A ,BOOK B 
     WHERE A.author_id = B.author_id 
     GROUP BY b.author_id) C , 
author A, 
BOOK B 
WHERE A.author_id = B.author_id 
    AND B.author_id=C.Author_id
    AND date_release BETWEEN NOW() - INTERVAL 20 YEAR AND NOW()
GROUP BY c.author_id
HAVING contar > 0

As you see there's two tables BOOK that has book_id, author_id, book_name, date_release, and table AUTHOR author_id, author_name, author_lastname, date_birth.

I'm getting this results

But I need something like

--------+-------+--+
| contar | final |  |
+--------+-------+--+
|      1 |    -4 |  |
|      2 |    -3 |  |
|      5 |     0 |  |
|      1 |    -4 |  |
+--------+-------+--+ 

Answer 1

The problem is that MAX(C.qty) is being calculated per-author, because the query has GROUP BY c.author_id. You need to calculate this total just once for the entire subquery.

There's also no need to join with AUTHOR when getting the count of books per author, since the author ID is in BOOK.

SELECT  count(book_name) contar, (max_qty - count(book_name)) final
FROM (SELECT MAX(qty) AS max_qty
      FROM (select count(book_name) qty 
            FROM BOOK B 
            GROUP BY b.author_id) t
     ) C 
CROSS JOIN author A
JOIN book B ON A.author_id = B.author_id 
WHERE date_release BETWEEN NOW() - INTERVAL 20 YEAR AND NOW()
GROUP BY A.author_id

And there's no need for HAVING contar > 0, because authors with no books will not be included in the JOIN.