How can I correct this query that involves a CASE statement for a summary?

I'm currently trying to solve an issue revolving around summarizing a list of publishers, their total revenue, total payouts, and their net profit. What makes this slightly complicated is that the total payout is contingent on a case statement (due to having to choose between the higher value of royalties). This case statement was perfectly fine and executed in a previous query that you can see on the SQLFiddle link down below. My issue is that I have a near finished query that addresses what I need but I don't know what correction to make for it to complete. Help would be super appreciated! And if you get it to work, you would be a legit lifesaver!!

Select name,
SUM(book.msrp) AS 'Total Revenue',
SUM(EarningByBook) AS 'Total Payouts',
SUM(book.msrp)-SUM(EarningByBook) AS 'Net Profit'
FROM
(SELECT publisher.name, book.msrp,
(SELECT
CASE WHEN preferred_royalties > standard_royalties
THEN preferred_royalties*copies_sold
ELSE standard_royalties*copies_sold END
AS 'EarningByBook',
copies_sold ,
YEAR(CURDATE())-YEAR(date_published) Years
INNER JOIN book ON publisher.id = book.publisher_id)
FROM author A
JOIN book B ON A.id=B.author_id
JOIN publisher P ON B.publisher_id=P.id)
From publisher
INNER JOIN book ON publisher.id = book.publisher_id) Z
GROUP BY
name;


The SQL fiddle is as follows : https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=911696b1a3dfd3a9f090db2766a8e96f&hide=13312

The output expected should look

Publisher Total Revenue Total Payouts Net Profit
name 20000 1500 18500
name 15000 1000 14000

Continue reading...